Follows on from #1413. Draft — the shape is up for discussion.

Why filter and not where

You asked whether the lookup syntax should go into Table.where too. I don't think it should:
kwargs can only ever mean AND, and where can do much more than that.

# `where` today - fine
Band.objects().where((Band.name == "Pythonistas") | (Band.name == "CSharps"))

# the same thing as kwargs - impossible, there's no way to spell OR
Band.objects().where(name="Pythonistas", ???="CSharps")

Django hit this exact wall, which is why it has Q objects. Teaching where the kwargs syntax
would import that problem into the part of Piccolo that doesn't have it. A separate filter is
honest about being the limited one:

Band.filter(popularity__gte=1000)              # AND of lookups, that's all it does
Band.filter(popularity__gte=1000).where(...)   # need more? `where` is right there

What it does

# these two are the same query
await Band.filter(popularity__gte=1000, manager__name="Guido")

await Band.objects().where(
    Band.popularity >= 1000,
    Band.manager.name == "Guido",
)

It returns Objects, so it chains as usual:

await Band.filter(popularity__gte=1000).order_by(Band.name).first()

The payoff is that a lookup from a payload is the lookup you pass to the query:

# GET /bands?popularity__gte=1000&manager__name=Guido
criteria = parse_params(request.query_params)   # allow-list + convert, see below
return await Band.filter(**criteria)

criteria for OR

await Band.filter(
    Band.criteria(manager__name="Guido") | Band.criteria(manager__name="Graydon"),
    popularity__gte=1000,
)

This is Django's Q, but it doesn't need to be a lazy expression tree the way Django's is —
Piccolo's expressions already compose, so criteria is just a constructor for clauses you
already have. Which means it drops into where untouched:

await Band.objects().where(
    Band.criteria(popularity__gte=1000) | (Band.name == "Pythonistas")
)

That felt like a better answer to your question than putting lookups in where: you get the
data-driven case and keep one expression language.

On Table rather than a compat module

I tried it as a compat.django mixin first, per your suggestion, and moved it back for three
reasons:

• A mixin only reaches tables you define. BaseUser, SessionsBase, piccolo_api's tables,
  anything from table_reflection — you can't add a mixin to those, and "filter this from query
  params" lands on them often.
• Model(Table) doesn't work. objects can't become a property: it's a classmethod taking
  *prefetch, called with arguments in piccolo/query/methods/objects.py:107 and on user table
  classes in piccolo/columns/m2m.py:366, plus six places in piccolo_api. And with no abstract=
  flag on __init_subclass__, Model itself becomes a table called model that table_finder
  picks up.
• A non-Table mixin can't type its own cls. Both mypy and pyright reject
  cls: type[TableInstance] on one ("the erased type of self is not a supertype of its class"),
  so it needs a Protocol scaffold that disappears entirely inside Table.

filter is the 25th classmethod on Table, and shadows a same-named column exactly the way
select/objects/delete already do — I checked, a column called select works fine today.
There's an assert_type in tests/type_checking.py confirming await Band.filter(...) still
infers list[Band].

Happy to move it back to compat.django if you'd rather — it's the same code either way.

Lookups

field[__related_field...][__transform][__op]

A column always beats a suffix sharing its name, so both of these work:

Event.filter(year=2020)          # the `year` column
Event.filter(starts__year=2020)  # the `year` transform

Two things worth knowing:

• Values aren't converted, so query params (always strings) need handling first —
  popularity__gte="1000" works on SQLite and fails on Postgres. That's true of
  where(Band.popularity >= "1000") too; filter doesn't change it. Should it convert against
  column.value_type? Django does. I left it out as it's a bigger decision.
• Untrusted lookups need an allow-list — otherwise a client can filter on any column, and across
  foreign keys into other tables. Documented with a warning.

Left out on purpose, happy to add: exclude(), ~criteria (needs __invert__ on
CombinableMixin), __isnull, __contains.

Notes

• Datetime transforms return a QueryString, where | means COALESCE, not OR — so
  criteria normalises them to WhereRaw the way WhereDelegate.where does. There's a test.
• update/delete can't join, so Where rewrites a clause on a related column into a sub
  select. WhereRaw doesn't, so a transform over a foreign key needed a small JoinedWhereRaw
  in lookups.py that repeats the rewrite. If you'd rather that lived on WhereRaw itself,
  say so — it'd fix the same case for anyone passing a raw QueryString to where.
• piccolo_api hand-rolls this translation today — OPERATOR_MAP in crud/endpoints.py:51,
  applied around :790-825 — and its version can't traverse joins, because line 792 is a flat
  getattr(self.table, field_name).

Questions

1. criteria — good name? It's Q in Django, but there are no uppercase-named methods anywhere
   in Piccolo, and clause is the word the docs use most.
2. Is a second method worth it at all, or should OR just go through where with ordinary
   expressions? Dropping criteria is a clean retreat.
3. Value conversion — see above.

Tests pass on SQLite and Postgres; lint.sh and pyright are clean.