In SQL, a multi-tenant admin report has a dozen optional filters, some taking lists; how do you build the statement, and why not one catch-all predicate?
answer
- always-on predicates come first
- one fixed fragment per present filter
- placeholders and values in step
- one placeholder per list element
- NULL already means no filter
basics
~20 sStart from the fixed tenant predicate and append one bound predicate per filter present, expanding lists into placeholders or an array. A catch-all such as ($2 IS NULL OR col = $2) can waste indexes and cannot ask for NULL.
solid answer
~50 sStart with the predicates that always apply, `WHERE tenant_id = $1`, then for each filter actually present append a fixed fragment and its bound value, keeping placeholder numbers and the parameter list in step. A list filter needs one placeholder per element, `status IN ($2, $3)`, or in PostgreSQL one array parameter, `status = ANY($2)`, and an empty list must be handled, because `IN ()` is a syntax error on most engines. The catch-all, `($2 IS NULL OR status = $2)`, keeps one static text and suits two or three filters. With a dozen it makes one statement serve every combination, so whether an index on a filter column gets used depends on whether the engine can simplify those ORs for the values at hand. It also spends NULL on "no filter", so asking for rows whose status is NULL needs another form, and it cannot choose a table or sort column.
code
sql · 7 linesPREPARE by_status (int, text[]) AS
SELECT id, status
FROM orders
WHERE tenant_id = $1 AND status = ANY($2);
EXECUTE by_status(42, ARRAY['open', 'closed']);
EXECUTE by_status(42, '{}'); -- no rowsgo deeper
Recall the shape: one fixed tenant predicate, then one extra AND predicate for each filter the user set, with every value bound and never written into the text.
Explain the moving parts: keeping placeholder numbers in step with values, one placeholder per list element, and why an empty list differs from an absent one.
Compare honestly. A catch-all is fine for two or three filters and risky for a dozen, because index use depends on simplifying the OR. Say how you would test a builder without enumerating shapes.
Decide where the line sits for the team, say a filter count beyond which reports use the shared builder, so that the choice is not reargued for every new report.
## The problem Picture a multi-tenant admin report. An administrator can filter on status, date range, owner, region and several other columns, in any combination, and each filter is optional. The tenant is not optional. Twelve independent on-or-off choices give up to 4,096 combinations, so writing them out as fixed statements is impractical. There are two serious ways to handle this: **build the text** from the filters present, or write one **catch-all predicate** that switches itself off. ## The builder, step by step 1. **Start with what always applies.** `WHERE tenant_id = $1` is written once and no input can remove it. It must never be one of the optional parts, or an empty filter set reads every tenant's rows. 2. **Keep two things side by side:** the list of predicate fragments and the list of values. 3. **For each filter present,** append the value to the list and add a **fixed** fragment from the code that uses that value's position, such as `status = $2`. 4. **Join the fragments with `AND`.** The value never enters the text, only its placeholder does. 5. **Add the names last.** The table and sort column come from a fixed list held in code, since neither can be bound. ```sql -- No optional filters present SELECT id, status, total FROM orders WHERE tenant_id = $1 ORDER BY created_at DESC; -- Status and date filters present SELECT id, status, total FROM orders WHERE tenant_id = $1 AND status = $2 AND created_at >= $3 ORDER BY created_at DESC; ``` The `WHERE 1=1` idiom some builders use just lets every fragment begin with `AND`. It is harmless, and joining a list of fragments avoids needing it. ## The catch-all predicate The alternative is one static statement in which each filter disables itself when its parameter is NULL: ```sql SELECT id, status, total FROM orders WHERE tenant_id = $1 AND ($2::text IS NULL OR status = $2) AND ($3::date IS NULL OR created_at >= $3); ``` Its appeal is real. The text never changes, so it can be read, searched for and tested as one statement, with no string handling at all. For two or three filters on an already selective tenant predicate, it is often the right choice. It has three limits as the filters grow: - **Index use is not guaranteed.** The `OR` asks the server to handle both branches. In PostgreSQL, a plan made for the actual values folds `$2 IS NULL` away and can use an index on `status`, but a plan made without the values cannot use that index for this predicate and has to check it row by row. How a given engine and call chooses between those is covered with plan caching. The point here is that you are relying on that choice. - **NULL is spent.** NULL now means "no filter", so "rows whose status is NULL" cannot be expressed through this parameter. - **Shape is still out of reach.** It does nothing for the table or the sort column, which still have to go in the text. ## How they compare | | Builder | Catch-all predicate | |---|---|---| | Statement text | Varies with the filters | One fixed text | | Each statement holds | Only the predicates in use | Every predicate, always | | Index use on a filter | Plain predicate, as if written by hand | Depends on simplifying the `OR` | | Filtering for NULL | Add `IS NULL` as its own fragment | Needs another parameter | | Main risk | Numbering bugs, many shapes to test | Slow combinations, hidden until they run | ## List-valued filters A placeholder holds one value, so `status IN ($2)` bound to `'open,closed'` compares against a single string containing a comma and matches rows with exactly that status. There are two correct forms: - **One placeholder per element.** The builder generates `status IN ($2, $3, $4)` to match the list's length. This works everywhere, and the text varies with the length. - **One array parameter, in PostgreSQL.** `status = ANY($2)` with a `text[]` parameter keeps the text constant whatever the length. An **empty list** needs a decision before it needs code. It is not the same as an absent filter: absent means do not filter, while empty normally means the caller selected nothing, so no rows should match. `IN ()` is a syntax error in PostgreSQL and MySQL, though SQLite accepts it, so the builder must return early or add a predicate that is always false. PostgreSQL's `status = ANY('{}')` simply matches no rows. Lists of many thousands of values have problems of their own, which are a separate matter. ## Mistakes worth checking for - **Numbering drift.** Placeholders and values go out of step when a branch adds to one list and not the other. Deriving the number from the values list prevents this. - **Precedence.** A filter fragment holding an `OR` needs its own parentheses, or it escapes the tenant predicate. - **Testing only the full set.** Test each filter alone, none at all, and the pairs people actually use, rather than trying every combination.
- An id filter takes a list of any length. How do you bind it, given that one placeholder holds one value?Generate one placeholder per element, `id IN ($2, $3, $4)`, which works on every engine but varies the text with the length. In PostgreSQL you can bind one array instead, `id = ANY($2)`, which keeps the text fixed. A single string such as `'1,2,3'` is one value, so it does not expand into a list.
- What is the difference between an absent list filter and an empty one?Absent means the caller did not use this filter, so the predicate is left out and all rows pass it. Empty normally means the caller chose nothing, so no row should match. Treating empty as absent quietly returns everything. `IN ()` is a syntax error on PostgreSQL and MySQL, so the builder has to decide explicitly.
- Twelve optional filters allow 4,096 statement shapes. Does that make the builder impractical?Not by itself. Only combinations people actually use ever get built, usually a small fraction. The real costs are testing, which should target single filters, no filters and common pairs, and watching which shapes turn out slow. How many distinct statement texts a server can comfortably cache is a separate question.
- A catch-all predicate is always slower because it can never use an index.
- Binding 'a,b,c' to IN ($1) filters on those three values.
- An empty list can go out as IN () and every engine will match no rows.
- The tenant predicate can be one more optional fragment in the builder.
- A catch-all predicate also removes the need to build text for the sort column.
- Filter values can go straight into the text because the report is internal.