Query Builder and Scopes

The findAll(where="...") surface works for simple queries, but Wheels ships four more tools for the queries that outgrow a raw WHERE string. The chainable query builder composes conditions safely. Scopes name reusable fragments. Enums turn named property values into auto-generated checkers and scopes. And batch processing keeps you out of memory trouble on large result sets. This page covers all four.

You’ll learn:

• How to chain where, orderBy, limit into a single fluent query
• How to declare scopes that compose across a model
• How to declare dynamic scopes that accept parameters
• How to declare enums and use the checkers and scopes they generate
• How to stream large tables with findEach and findInBatches

Note

You should already know how to define models and use finders. See Models and the ORM.

Chainable query builder

Most finders accept where="..." as a raw string: model("Post").findAll(where="status = 'published' AND publishedAt <= NOW()"). That’s fine for a short condition, but it breaks down quickly. Strings require manual quoting, are painful to assemble conditionally, and are the natural home for SQL injection mistakes when user input enters the expression.

The query builder exposes where, orderBy, limit, and the rest as chainable methods on the model class. Each method returns a query object, so you keep composing until you call a terminal method — get(), first(), or count() — that runs the SQL. Values passed to builder methods are auto-quoted through the database adapter; there’s no string concatenation into SQL.

component extends="Controller" {
    function index() {
        posts = model("Post")
            .where("status", "published")
            .where("publishedAt", "<=", Now())
            .orderBy("publishedAt", "DESC")
            .limit(25)
            .get();
    }
}

The two-argument form of where means equality. The three-argument form takes an operator in the middle — >, <, >=, <=, !=, LIKE, and so on. Stack multiple .where(...) calls and they AND together. A single-argument where("status = 'published'") passes the clause through as you wrote it. Literals right after a comparison operator are still bound (see Which values are bound), but the rest is your SQL, so keep it to trusted clauses. Use the 2- or 3-argument forms for values that came from the request.

Builder methods

Method	SQL it produces
where(column, value)	column = value
where(column, operator, value)	column <operator> value
orWhere(...)	Same calling conventions as where, OR-combined with prior clauses
whereNull(column)	column IS NULL
whereNotNull(column)	column IS NOT NULL
whereBetween(column, low, high)	column BETWEEN low AND high
whereIn(column, list)	column IN (...) — empty list short-circuits the terminal to return no rows
whereNotIn(column, list)	column NOT IN (...) — empty list is a no-op (every row matches)
orderBy(column, direction)	ORDER BY column direction (direction defaults to ASC)
limit(n)	LIMIT n
offset(n)	OFFSET n
select(columns)	Restrict the SELECT list to the given columns
include(associations)	Join the named associations (same as findAll(include=...))
group(columns)	GROUP BY columns
distinct()	SELECT DISTINCT
forUpdate()	FOR UPDATE row locking (needs a transaction; no-op on SQL Server/SQLite)
get()	Executes, returns a query of all matching rows
first()	Executes, returns the first matching row
count()	Executes COUNT(*), returns an integer

Long IN lists. Every value of a whereIn() / whereNotIn() list is bound as its own parameter. Two databases limit that:

• Oracle 19c and earlier accept at most 1000 values per list. Wheels splits a longer list into groups for you.
• SQL Server accepts about 2,100 parameters per statement.
  • On SQL Server 2016 and later (database compatibility level 130 or higher), a statement over the limit runs its largest lists of integers, plain decimals (up to 38 digits), strings and uniqueidentifiers as one parameter each, expanded on the server with STRING_SPLIT.
  • Date and timestamp lists run the same way when every value is written as yyyy-mm-dd, optionally followed by HH:mm, :ss and .SSS (or is a CFML date). So do time lists written as HH:mm, optionally followed by :ss and a fraction (or CFML times). Each value is compared the way a short list compares it: in the column’s own type on a DATETIME, SMALLDATETIME or TIME column, otherwise as the database driver binds it. A long list therefore matches exactly the rows a short one would.
  • Any other long list (dates written another way, float, real, bit, text or binary values, or numbers written as +5, .5 or 1E5), or a database below level 130, fails before it runs with Wheels.TooManyParameters. Query those values in batches, or use a join or subquery instead of a long list.

Every builder method can start the chain directly on the model class — you don’t have to lead with where(). Starting with select() reads naturally when you only need a few columns:

component extends="Controller" {
    function directory() {
        people = model("Person")
            .select("id,firstName,lastName")
            .where("department", "engineering")
            .get();
    }
}

Here’s a longer example that exercises several of these:

component extends="Controller" {
    function search() {
        products = model("Product")
            .whereIn("categoryId", [1, 2, 3])
            .whereBetween("price", 10, 100)
            .whereNotNull("publishedAt")
            .orWhere("featured", true)
            .orderBy("name", "ASC")
            .limit(50)
            .offset(100)
            .get();

        totalMatches = model("Product")
            .whereIn("categoryId", [1, 2, 3])
            .whereBetween("price", 10, 100)
            .count();
    }
}

Tip

Values in builder calls are auto-parameterized through the model’s database adapter. That’s the point. Use the builder whenever user input flows into a query value — you get the readability of a fluent API and the safety of parameter binding in one call.

Which values are bound

In any where string, whether you wrote it or a builder method or dynamic finder built it, Wheels binds a quoted or numeric literal as a query parameter when it comes right after a comparison operator: =, <>, !=, <, >, <=, >=, LIKE, NOT LIKE, IN (...), NOT IN (...), IS or IS NOT.

A literal anywhere else stays in the SQL text, escaped for your database:

• a function argument: lastName = UPPER('smith')
• a hand-written range: price BETWEEN 10 AND 100 (whereBetween() binds both bounds)
• the character in LIKE '...' ESCAPE '!'
• every value, when you pass parameterize=false

Escaped is not the same as bound. Don’t build those positions from request input: compare with an operator instead, or use the builder.

Backslashes on MySQL and MariaDB

A backslash in a where value is an ordinary character, whether the value is bound or written into the SQL text. 'C:\temp' matches C:\temp, and 'C:\\temp' matches two backslashes, on MySQL just as on every other database. Before 4.2, values written into the SQL text (every value with parameterize=false, and function arguments) went through MySQL’s own backslash escapes. If you wrote \\ there to mean one backslash, write \ now.

The one exception is the escape character of LIKE ... ESCAPE: both ESCAPE '\' and the MySQL spelling ESCAPE '\\' mean a single backslash, in any sql_mode.

A value containing a backslash is written as a _utf8mb4 hex literal. Comparing it with a column of any character set or collation works as usual. Combining it with another literal (for example CONCAT('a', ...)) can raise Illegal mix of collations when the connection’s collation differs from utf8mb4’s default. Put the whole value in one literal, or bind it.

Searching for a literal term

A search box sends whatever the user typed, and a % or _ in that text is a LIKE wildcard, not a literal character — a search for 50% would match every row that merely contains 50. escapeForLike() escapes the \ escape character and the % and _ wildcards (and, on SQL Server, [) so the term matches literally. On SQL Server, [ is escaped only once a model has initialised the adapter — if you call escapeForLike() before any model() call in a fresh or just-reloaded app, [ is left unescaped; every other database treats [ as a literal anyway. Add your own surrounding wildcards and declare ESCAPE '\':

posts = model("Post").findAll(
    where = "title LIKE '%#escapeForLike(params.q)#%' ESCAPE '\'"
);

The quoted literal is bound as a parameter (parameterize is on), and the explicit ESCAPE '\' makes the escaped wildcards literal on every database. Always declare the clause: MySQL, PostgreSQL, CockroachDB and H2 default the LIKE escape character to \, but SQLite, Oracle and SQL Server have none, and the three-argument builder (where("title", "LIKE", ...)) emits no ESCAPE either, so without it a \ is matched literally. escapeForLike() escapes LIKE metacharacters only, not SQL quotes — quote the value the same way you would any other where-string literal.

Scopes — reusable query fragments

Scopes are named pieces of a query that you declare once on a model and call as methods on the model class. model("User").active(). model("Post").published().recent(). They chain, compose with each other, and compose with the query builder. When the same filter shows up on two or more pages, turn it into a scope.

Declare scopes in config() using the scope() function. The name argument is what you’ll call on the model class. The where and order arguments hold the SQL fragments.

component extends="Model" {
    function config() {
        scope(name="active", where="status = 'active'");
        scope(name="recent", order="createdAt DESC");
    }
}

With those two declarations, every Wheels finder on User gains .active() and .recent() methods. Chain them and each adds to the accumulated query:

component extends="Controller" {
    function index() {
        users = model("User").active().recent().findAll();
        totalActive = model("User").active().count();
    }
}

Scopes compose with the query builder the same way — a scope call returns a chainable query object, so .where(...) picks up where the scope left off.

Dynamic scopes

Static where strings can’t accept parameters. When a scope needs an argument — “filter by this role”, “posts after this date” — declare it with a handler instead. The handler is a private method on the model that returns a struct of query options. Anything you’d pass to findAll — where, order, include, select — is a valid key.

component extends="Model" {
    function config() {
        scope(name="byRole", handler="scopeByRole");
    }

    private struct function scopeByRole(required string role) {
        return { where: "role = '#arguments.role#'" };
    }
}

Call it like any other scope: model("User").byRole("admin").findAll(). Arguments passed to the scope call flow straight into the handler.

Caution

Handler-returned where strings are still strings — if the argument came from user input, interpolate it only after you’ve validated or escaped it. For user-supplied values, pair the scope with a builder call: model("User").byRole("admin").where("status", params.status).get(). Wheels applies a built-in sanitizer to scope handler arguments across all invocation paths — direct (model.byRole(input)), scope-chained (model.someScope().byRole(input)), and builder-chained (model.where(...).byRole(input)) — but treat that as a safety net, not a substitute for validating user input before it reaches a where string.

Enums — named property values

Enums declare that a property takes one of a fixed set of named values. Wheels auto-generates three things when you declare one: an inclusion validation so the value is always valid, a boolean checker method per value on model instances (post.isDraft(), post.isPublished()), and a scope per value on the model class (model("Post").draft(), model("Post").published()). You write one line of config; the framework wires the rest.

Values can be declared two ways. The list form maps each name to an identical stored string. The struct form lets you map names to different stored values — an integer, a different string, whatever you want in the database.

component extends="Model" {
    function config() {
        enum(property="status", values="draft,published,archived");
    }
}

The list form above stores the literal strings "draft", "published", and "archived" in the status column.

component extends="Model" {
    function config() {
        enum(property="priority", values={low: 0, medium: 1, high: 2});
    }
}

The struct form stores integers 0, 1, or 2 in the priority column, but you interact with them by name everywhere in your code.

Once declared, the generated checkers and scopes are available with no extra setup:

component extends="Controller" {
    function index() {
        draftPosts = model("Post").draft().findAll();
        publishedPosts = model("Post").published().findAll();
    }

    function show() {
        post = model("Post").findByKey(params.key);
        if (post.isDraft()) {
            redirectTo(route="posts", error="Not published yet");
        }
    }
}

Declare as many enums per model as you need. Each property gets its own checkers and scopes.

Batch processing — findEach and findInBatches

Loading a hundred thousand rows with findAll() is a memory disaster. The batch APIs load and yield in chunks so the whole result set never sits in memory at once. Reach for them whenever you’re iterating a result set that could outgrow what fits comfortably in RAM — a good rule of thumb is 10,000 rows or more, but lower if each row is wide.

There are two options. findEach calls your callback once per record, loading and discarding batches transparently. findInBatches calls your callback once per batch, handing you a query of up to batchSize rows. Use findEach when each record is processed independently; use findInBatches when you’re doing something like a bulk write or a CSV export that benefits from seeing groups at a time.

component extends="Model" {
    public void function resendAllReminders() {
        findEach(batchSize=1000, callback=function(user) {
            arguments.user.sendReminderEmail();
        });
    }
}

findEach defaults to batchSize=1000 and orders by the primary key ascending so pagination stays consistent across batches. The callback receives one model instance at a time.

component extends="Model" {
    public void function exportAll() {
        findInBatches(batchSize=500, callback=function(users) {
            // arguments.users is a query of up to 500 rows
            exportToCSV(arguments.users);
        });
    }
}

findInBatches defaults to batchSize=500 and yields a query per invocation by default. Pass returnAs="objects" or returnAs="structs" if you’d rather receive an array.

Both methods accept the same filter arguments as findAll — where, order, include, select. And both compose with scopes: model("User").active().findEach(batchSize=500, callback=function(user) { ... }) streams only the active users.

Combining features

Scopes, enums, and the builder all return the same chainable query object, so they compose freely. A controller action pulling the dashboard of published posts owned by the current user reads like English:

component extends="Controller" {
    function dashboard() {
        posts = model("Post")
            .published()
            .where("userId", session.userId)
            .orderBy("publishedAt", "DESC")
            .limit(10)
            .get();
    }
}

.published() is generated by an enum on Post. .where(...) and .orderBy(...) and .limit(...) are builder methods. .get() is the terminal that runs the SQL. One chain, all four tools.

When to use what

The four features overlap — you could write most queries with just the raw where string if you wanted. The guidance below is about readability and safety, not strict rules.

• Raw where="..." string. Simplest case. One or two conditions, no user input being interpolated into the SQL. model("Post").findAll(where="status = 'published'", order="publishedAt DESC").
• Query builder. Multiple conditions, conditional assembly based on search form input, or anywhere user input flows into a value. The auto-quoting is the safety net.
• Scopes. When the same query fragment appears on two or more pages, give it a name. You’ll thank yourself the first time the filter changes.
• Enums. When a property takes one of a fixed set of named values. The auto-generated checkers and scopes save boilerplate everywhere the property is read.
• Batch processing. When the result set is too big to fit in memory comfortably. Rule of thumb: 10,000 rows or more, or fewer if each row is wide.

Related guides

Models and the ORM The model surface this page builds on — finders, persistence, validations.

Associations Scopes and the builder chain onto association queries the same way they chain onto the model class.

Migrations Defining the columns and indexes the queries on this page run against.