List Filters Array Contains ?

[SemihYlmzz]

Hey, is there a filter that i can search value in a List like "array-contains" in Firestore. Mostly i am looking for "array-not-contains" which even Firestore does not support. I looked little bit but this "ColumnSerializable" do not have that much function to filter.

Example:

Post.db.find(
      session,
      where: (table) => table.likerIDList.contains(myId),
);

If there is no such method making this filter is it make sense to make all id's as a String and searching in ?

String likerIDList = 'id1,id2,id3';
Then i will search in text to find my id in it or not.

Thanks for your time.

[Isakdl]

If the list is a relation field you are able to do that with the any method.

See: https://docs.serverpod.dev/concepts/database/filter#any
and how to setup a relation: https://docs.serverpod.dev/concepts/database/relations/one-to-many

Post.db.find(
      session,
      where: (table) => table.likerIDList.any((o) => o.id.equals(myId)),
);

However, if you have stored the list as a json blob then currently there is no built-in support to query for specific items within the json blob.

But you can always write a custom query using the unsafeQuery method.
https://docs.serverpod.dev/concepts/database/raw-access

For your use case the query could look something like this:
https://stackoverflow.com/questions/19925641/check-if-a-postgres-json-array-contains-a-string

But if you use unsafeQuery make sure you sanitize any dynamic input as you otherwise may open yourself to SQL injection attacks.

[Isakdl]

@SemihYlmzz Just to add that if you would like a not contains you can do that with a relation field in the following way:

Use the every method (every entry must match the condition) combined with notEquals.

Post.db.find(
      session,
      where: (table) => table.likerIDList.every((o) => o.id.notEquals(myId)),
);

This query means that every entry in likerIDList must not be equal to myId, e.g. does not contain myId.