piccolo-orm / piccolo

A fast, user friendly ORM and query builder which supports asyncio.
https://piccolo-orm.com/
MIT License
1.32k stars 86 forks source link

Add `not_any` method to `Array` #1021

Closed dantownsend closed 2 weeks ago

dantownsend commented 2 weeks ago

We currently have an any method for Array columns. It lets us get rows where the array column contains the given value:

class MyTable(Table):
    array_column = Array(Integer())

>>> await MyTable.select(MyTable.array_column).where(MyTable.array_column.any(1))
[{"array_column": [1, 2, 3]}]

It would be nice to have the inverse of this, so we can get all matching rows which don't contain the given value:

>>> await MyTable.select(MyTable.array_column).where(MyTable.array_column.not_any(1))
[{"array_column": [4, 5, 6]}]

In the future we could add a Not function, so this syntax would work:

>>> await MyTable.select(MyTable.array_column).where(Not(MyTable.array_column.any(1)))

But this requires us to refactor the where clause a bit, so it's all based around QueryString, so can come later. Even after we've added a Not function, having a not_any method is still a convenient thing to have.