Beyond findAll — Scopes, Enums, and the Chainable Query Builder

[bpamiri]

Fourth in the post-GA series. The rate-limiter post took the middleware pipeline, the packages post took the extension model, the MCP post took the AI surface, and this one takes the model-side query story. Specifically: what to reach for instead of findAll(where="...") once the raw WHERE strings stop scaling.

Read: https://blog.wheels.dev/posts/beyond-findall-scopes-enums-query-builder

The post walks three features that look separate but compose into one design:

• Scopes — scope(name="published", where="status = 'published'", order="publishedAt DESC") registers a named query fragment. model("Post").published().findAll() returns a ScopeChain proxy, which onMissingMethod() hands the registered spec struct. Chain multiple scopes and $mergeSpecs() rolls them up before the terminal call. Dynamic scopes take parameters via a handler that returns a spec struct with whereParams — the safe path for user input, not string interpolation.
• Enums — enum(property="status", values="draft,published,archived") is one declaration that registers a validatesInclusionOf, three boolean checkers (isDraft, isPublished, isArchived) on every instance via onMissingMethod(), and three scopes (.draft(), .published(), .archived()) on the model class. The auto-generated scopes are parameterised — where: "status = ?" plus whereParams = [{value: "published", type: "CF_SQL_VARCHAR"}] — not string-interpolated. Two value forms: comma-list (names map to themselves) and struct (names map to explicit stored values).
• The chainable query builder — where("col", value) is auto-quoted and type-checked. Each property has a declared validation type (integer, float, boolean, date, string); values get regex-validated against that type before any SQL is built, so the classic "0 OR 1=1" payload fails the type check before the binding layer ever sees it. Full method surface: where, orWhere, whereNull, whereNotNull, whereBetween, whereIn, whereNotIn, orderBy, limit, offset, select, include, group, distinct, forUpdate. Terminals: .get(), .first(), .count(), .exists(), .updateAll(), .deleteAll(), .findEach(), .findInBatches().
• They compose because they share machinery — all three return objects that implement onMissingMethod() and accumulate state into the same finder-argument struct. $buildFinderArgs() materialises the chain on a terminal call and hands the result to the existing findAll(). The chainable surface is sugar around the existing finder, not a parallel implementation.

Side note: a framework bug surfaced while writing this

Drafting the post, I tried model("Post").whereIn("id", []) to see what the framework did with an empty array. The answer: it emitted literal SQL id IN (), which is malformed in every supported engine — Postgres, MySQL, SQL Server, SQLite, H2 — and surfaced as a generic JDBC syntax error with no pointer back to the call site that built the empty array.

Empty inputs to WHERE IN aren't exotic. They're what you get whenever the values come from another query, a form filter, or any computation that might return zero results. Rails converged on this pattern in 2016, Sequel matches it, Django matches it, Laravel Eloquent matches it: an empty IN matches no rows, and an empty NOT IN matches every row.

That's #2736, fixed in the same week the article landed. whereIn("id", []) sets an $alwaysEmpty flag on the builder so every terminal (.count(), .findAll(), .first(), .exists(), .updateAll(), .deleteAll(), .findEach(), .findInBatches()) short-circuits to the appropriate zero-row sentinel before going through the finder. whereNotIn("id", []) is a no-op so the chain proceeds normally. The first cut tried the obvious raw-SQL approach (append 1 = 0 / 1 = 1 as clauses) but Wheels' WHERE-clause parser runs a property-extraction regex over every clause it sees and threw Wheels.ColumnNotFound on the literal 1. The flag-based design works alongside the parser instead of around it. Fourteen new specs lock the behaviour in. The reference table in both copies of the query-builder guide was updated so a reader skimming the methods doesn't have to read the source to know what happens on empty input.

Three related rough edges are flagged in the post but not fixed in this PR:

• No .toSql() debugging helper. If you want to see the SQL the chain is about to generate, you have to enable the debug panel or step through $buildFinderArgs(). A .toSql() method that returns the would-be query string without executing it would be a useful affordance. Filed for follow-up.
• No defaultScope() / unscoped(). Rails has both; Wheels has neither. Soft-delete is the obvious motivating case — without a default scope, you scatter .whereNull("deletedAt") through every call site.
• Enum value-name collisions are unchecked. enum(property="action", values="create,update,delete") will silently shadow the model's own update() and delete() chain methods. A registration-time guard could reject this; today there isn't one.

What's next in the post-GA series

The last title in the second batch:

1. From Empty Directory to Deployed SaaS — end-to-end with generators, multi-tenancy, jobs, browser tests, wheels deploy

Feedback on the query-side post — what's confusing, what's missing, what you'd want a future post to cover — welcome in this thread. The author-facing reference guide lives at https://guides.wheels.dev/v4-0-1-snapshot/basics/query-builder-and-scopes/ if you want the full field-by-field treatment.