Deeply nested top-filter fails when wrapped in a "or"

[noobmaster19]

Environment

• PostgreSQL version: (if using docker, specify the image) 14
• PostgREST version: (if using docker, specify the image) latest version
• Operating system: MacOS

Description of issue

Doing the following query:

curl --get "http://localhost:6060/customer" \
  -d "limit=20" \
  -d "offset=0" \
  -d "order=customer_name.asc" \
  -d "select=*,quotationCustomers(quotation%21inner(associated_personnel_ids))" \
  -d "or=(created_by.eq.1456f47e-62d5-4ecb-92aa-5922ee576086,quotationCustomers.quotation.associated_personnel_ids.cs.%7B1456f47e-62d5-4ecb-92aa-5922ee576086%7D)"

fails with the following error:

{"code":"PGRST100","details":"unexpected \"q\" expecting \"not\" or operator (eq, gt, ...)","hint":null,"message":"\"failed to parse logic tree ((created_by.eq.1456f47e-62d5-4ecb-92aa-5922ee576086, quotationCustomers.quotation.associated_personnel_ids.cs.{1456f47e-62d5-4ecb-92aa-5922ee576086}))\" (line 1, column 74)"}% 

Doing this:

curl --get "http://localhost:6060/customer" \
-d "limit=20" \
-d "offset=0" \
-d "order=customer_name.asc" \
-d "select=*,quotationCustomers(quotation%21inner(associated_personnel_ids))" \
-d "quotationCustomers.quotation.associated_personnel_ids=cs.%7B1456f47e-62d5-4ecb-92aa-5922ee576086%7D"

works.

(Expected behavior vs actual behavior)
I should have all customers, that's filtered by the resource embedding, quotationCustomers.quotation.associated_personnel_ids (an array)

[laurenceisla]

What you want to do is an OR filtering across embedded resources, although for deeply nested resources.

You should change your query to something like:

curl ...
  -d "select=*,qc:quotationCustomers(q:quotation()),quotationCustomers(quotation!inner(associated_personnel_ids))" \
  # Edit: I made a mistake here, it should be `=` instead of `.`
  -d "qc.q.associated_personnel_ids=cs.%7B1456f47e-62d5-4ecb-92aa-5922ee576086%7D" \
  -d "or=(created_by.eq.1456f47e-62d5-4ecb-92aa-5922ee576086,qc.not.is.null)"

The empty embed (the one that has the qc and q aliases) is used for filtering and the other one is used to get the data. Check these discussions for more info: #3333, #3663

[noobmaster19]

Hey, thanks for replying. To be honest, I'm quite confused, is there a reason why qc.not.is.null is used over qc.q.not.is.null. Also, since we use the not.is.null method, is there a need to put the !inner here: quotationCustomers(quotation!inner(associated_personnel_ids))

[noobmaster19]

From my understanding this is how it works:
"qc.q.associated_personnel_ids.cs.%7B1456f47e-62d5-4ecb-92aa-5922ee576086%7D" :

• This condition will filter the quotations records to only show those that contains the specified ID, but does nothing to the quotationCustomer OR the customer records.

"or=(created_by.eq.1456f47e-62d5-4ecb-92aa-5922ee576086,qc.not.is.null)"

• This condition is used to filter the customer records that are EITHER created_by the ID, or it's quotationCustomer field is non-empty.

"quotationCustomers(quotation!inner(associated_personnel_ids))"

• I'm not sure where this fits but according to the docs, it will filter the quotation records if we do something like: "quotationCustomers.quotation.associated_personnel_ids.cs.", but that's not done here since we used the empty embedding.

[laurenceisla]

Note that my answer was a tentative one, since I'm not sure about the details of the response. Let's forget about my original answer for a moment and go step by step to find a better one:

1. We want to filter the TOP resource customers by a nested embedded resource. Suppose we want to select only columns from the TOP resource first, so we use empty embeds. Using your original filter we could do something like this:

   curl -- get 'http://localhost:3000/customers'
     -d 'select=*,qc:quotationCustomers(q:quotation())'
     -d 'qc.q.associated_personnel_ids=cs.%7B1456f47e-62d5-4ecb-92aa-5922ee576086%7D'

   But, as you mentioned before, this will not filter customers nor qc:quotationCustomers. We could add -d 'qc.q=not.is.null' but this will only filter qc:quotationCustomers. So we need to add qc=not.is.null too, THIS is the one that effectively filters customers. The final request would be:

   curl -- get 'http://localhost:3000/customers'
     -d 'select=*,qc:quotationCustomers(q:quotation())'
     -d 'qc.q.associated_personnel_ids=cs.%7B1456f47e-62d5-4ecb-92aa-5922ee576086%7D'
     -d 'qc.q=not.is.null'
     -d 'qc=not.is.null'

2. Now we also want to include another condition for the TOP resource, so that the previous condition OR this new one are true. Remember that the condition that effectively filters customers is qc=not.is.null, so we add that to the or:

   curl -- get 'http://localhost:3000/customers'
     -d 'select=*,qc:quotationCustomers(q:quotation())'
     -d 'qc.q.associated_personnel_ids=cs.%7B1456f47e-62d5-4ecb-92aa-5922ee576086%7D'
     -d 'qc.q=not.is.null'
     -d 'or=(created_by.eq.1456f47e-62d5-4ecb-92aa-5922ee576086,qc.not.is.null)'

3. That should be all if you only want to select the columns of customers, since the TOP resource is already filtered. If you want to show the associated_personnel_ids and don't care if they return null (or an empty array) when the condition is not met (i.e. for some values returned by created_by.eq.<...>), then you can update the select to

   -d 'select=*,qc:quotationCustomers(q:quotation(associated_personnel_ids))'

4. If you still want to show the associated_personnel_ids for all the conditions that are met, you must add another embedd to show those values, e.g.:

   -d 'select=*,qc:quotationCustomers(q:quotation()),quotationCustomers(quotation!inner(associated_personnel_ids))'

   This new embedding is independent from the previous empty embed. The quotation!inner will not shape the TOP customers only the quotationCustomer that will be shown in the response. If that's not what you want you can remove it, refine the filter, or omit this step altogether.