Related `and/or` conditions

[steve-chavez]

Do you mean that if order=clients.name,name,clients.rank were used, then we should also consider to support or=(clients.name.like.Chris*,and(name.eq.TopProject,clients.rank.gt.1)?

It is indeed hard to read, and or=(clients(name).like.Chris*,and(name.eq.TopProject,clients(rank).gt.1) seems slightly better.

Originally posted by @Iced-Sun in https://github.com/PostgREST/postgrest/issues/1414#issuecomment-986187388

Also previously discussed on https://github.com/PostgREST/postgrest/discussions/2014

[steve-chavez]

Having the client, clientinfo and contact tables:

$ postgrest-with-postgresql-15 psql

postgres=# table test.clientinfo;
 id | clientid |      other
----+----------+------------------
  1 |        1 | 123 Main St
  2 |        2 | 456 South 3rd St
  3 |        3 | 789 Palm Tree Ln
(3 rows)

postgres=# table test.clientinfo;
 id | clientid |      other
----+----------+------------------
  1 |        1 | 123 Main St
  2 |        2 | 456 South 3rd St
  3 |        3 | 789 Palm Tree Ln
(3 rows)

postgres=# table test.contact;
 id |     name      | clientid
----+---------------+----------
  1 | Wally Walton  |        1
  2 | Wilma Wellers |        1
  3 | Tabby Targo   |        2
  4 | Bobby Bots    |        3
  5 | Bonnie Bits   |        3
  6 | Billy Boats   |        3
(6 rows)

I'd like to get the client rows where clientinfo.other LIKE %Main% OR contact.name LIKE %Tabby%.

Keeping the shape of our query, this could be done like:

  WITH pgrst_source AS (
    SELECT
      "test"."client".*
    FROM "test"."client"
    LEFT JOIN LATERAL (
      SELECT json_agg("client_clientinfo_1") AS "client_clientinfo_1"
      FROM (
        SELECT
          "clientinfo_1".*
        FROM "test"."clientinfo" AS "clientinfo_1"
        WHERE
        "clientinfo_1"."clientid" = "test"."client"."id" AND
        "clientinfo_1"."other" LIKE '%Main%'
      ) AS "client_clientinfo_1"
    ) AS "client_clientinfo_1" ON TRUE
    LEFT JOIN LATERAL (
      SELECT json_agg("client_contact_1") AS "client_contact_1"
      FROM (
        SELECT "contact_1".*
        FROM "test"."contact" AS "contact_1"
        WHERE
        "contact_1"."clientid" = "test"."client"."id" AND
        "contact_1"."name" LIKE '%Tabby%'
      ) AS "client_contact_1"
    ) AS "client_contact_1" ON TRUE
    WHERE
      "client_clientinfo_1" IS NOT NULL OR
      "client_contact_1" IS NOT NULL
  )
  SELECT coalesce(json_agg(_postgrest_t), '[]')::character varying AS body
  FROM ( SELECT * FROM pgrst_source ) _postgrest_t;

             body
------------------------------
 [{"id":1,"name":"Walmart"}, +
  {"id":2,"name":"Target"}]

Which could be expressed in the URL as:

GET /client?select=*,clientinfo(),contact()&clientinfo.other=like.*Main*&contact.name=like.*Tabby*&or=(clientinfo.not.is.null,contact.not.is.null)

So the good news is that basically we only need to support doing embed=not.is.null, which is also a replacement for !inner.

[steve-chavez]

Reopening. It's a bit inconvenient(also unclear) to type the not null condition on the resource.

Related conditions would be more clear as per the original idea above.

So I think we could translate this:

GET /projects?select=name,clients(*)&clients(id)=eq.1

To:

GET /projects?select=name,clients(*)&clients.id=eq.1&clients=not.is.null

---

or would also be clearer as

GET /client?select=*,clientinfo(),contact()&or=(clientinfo(other).like.*Main*,contact(name).like.*Tabby*)

Then translated to

GET /client?select=*,clientinfo(),contact()&clientinfo.other=like.*Main*&contact.name=like.*Tabby*&or=(clientinfo.not.is.null,contact.not.is.null)

[thomasdohyunkim]

Hi there. Just wanted to check if there is any support for this in the works or any available workarounds to chain multiple OR filters on sub tables? I came here from this stackoverflow question.

[laurenceisla]

@thomasdohyunkim AFAIK this is already implemented on a pre-release, but there's an issue with the clarity of the queries as Steve mentioned above. Here's an example on how to use it.

The latest pre-release has this feature available.

[Etwenn]

Hello, i need to do almost the same thing, i.e : table1 : fieldA, fieldB + Foreign key to table2 table2 : fieldD, fieldE

i want to do :

GET /table1?select=*,table2(*)&or=(table2.fieldD.eq.thing,table2.fieldE.eq.thing)

you would tell me to use this sentence i suppose : GET /table1?select=*,table2(*)&table2.or=(fieldD.eq.thing,fieldE.eq.thing)

but sometime i need to do :

GET /table1?select=*,table2(*)&or=(table2.fieldD.eq.thing,table2.fieldE.eq.thing,fieldA.eq.thing)

Here i'm stuck because i don't see how to use @steve-chavez workarround with multiple field of table2 + one field of table A

Do you have an idea ?

[laurenceisla]

you would tell me to use this sentence i suppose : GET /table1?select=*,table2(*)&table2.or=(fieldD.eq.thing,fieldE.eq.thing)

@Etwenn Yes, and if you need to exclude the null embeddings (top level filtering), add !inner or table2=not.is.null to the query string. Both of these return the same result:

GET /table1?select=*,table2!inner(*)&table2.or=(fieldD.eq.thing,fieldE.eq.thing)

GET /table1?select=*,table2(*)&table2.or=(fieldD.eq.thing,fieldE.eq.thing)&table2=not.is.null

but sometime i need to do :

GET /table1?select=*,table2(*)&or=(table2.fieldD.eq.thing,table2.fieldE.eq.thing,fieldA.eq.thing)

In this case, the filter needs to be in a separate query parameter and after that include the table2=not.is.null inside the or filter. It would be something like this:

GET /table1?select=*,table2(*)&table2.or=(fieldD.eq.thing,fieldE.eq.thing)&or=(fieldA.eq.thing,table2.not.is.null)

NOTE: The above query is related to the issue #2800 that you opened. If the row has a column with a null value, then it won't be included in the response (when it should). But I don't see a workaround for this case.

[steve-chavez]

FYI, or conditions across embedded resources have been possible for a while now with https://postgrest.org/en/latest/references/api/resource_embedding.html#or-filtering-across-embedded-resources.

Though we can still improve the syntax to be more straightforward