Using OR operator on columns of joined tables

[PAbernet]

Environment

• PostgreSQL version: docker.io/bitnami/postgresql:11.14.0-debian-10-r17
• PostgREST version: postgrest/postgrest:v10.1.1
• Operating system: Docker

Description of issue

Including a column of a joined table in an OR operator results in PostgREST parsing the column/table as operator, due to it strictly adhering to the schema of column.operator.parameters, when it would need to be table.column.operator.parameters. It could really be an arbitrary number of tables preceding the filtered column.

CREATE TABLE table1 (
  id SERIAL PRIMARY KEY,
  value1 INT,
  value2 INT
);

CREATE TABLE table2 (
  id SERIAL PRIMARY KEY,
  value1 INT,
  value2 INT
);

CREATE TABLE table3 (
  id SERIAL PRIMARY KEY,
  table1_id integer,
  table2_id integer,
  FOREIGN KEY (table1_id) REFERENCES table1 (id),
  FOREIGN KEY (Table2_id) REFERENCES table2 (id)
);

The SQL query I'm trying to replicate:

SELECT id 
FROM table3 
  JOIN table1 ON table1_id=table1.id 
  JOIN table2 ON table2_id=table2.id 
WHERE table1.value1 > 1 OR table2.value2 > 5;

curl 'http://localhost/table3' -G -d or='(table1.value1.gt.1,table2.value2.gt.5)'

Is there a way to get the desired behaviour, short of creating a view?

[laurenceisla]

If I'm understanding correctly, you want to filter the result by the columns from the joined table, right? PostgREST allows this with ebedded filters. For your case it should be something like this:

curl 'http://localhost:3000/table2?select=*,table1(*)&table1.or=(value1.gt.1,value2.gt.5)'

If you want to filter the top level rows you need to add !inner to the embedding:

curl 'http://localhost:3000/table2?select=*,table1!inner(*)&table1.or=(value1.gt.1,value2.gt.5)'

If this isn't what you meant, then add an SQL example of what you need to check it out.

[steve-chavez]

OR on different tables works like described here #2584

Allows using or across related tables conditions(#2014 (comment))
curl -G 'localhost:3000/client'
-d "select=*,clientinfo(),contact()"
-d "clientinfo.other=ilike.main&contact.name=ilike.tabby"
-d "or=(clientinfo.not.is.null,contact.not.is.null)"

[PAbernet]

That only seems to work on entire tables, though. Not on columns of joined tables.

$ curl 'http://localhost:3000/table3' -G -d select='*,table1()' -d or='(table1.value1.gt.1,table2.value2.
gt.5)'
{"code":"PGRST100","details":"unknown single value operator value1","hint":null,"message":"\"failed to parse logic tree ((table1.value1.gt.1,table2.value2.gt.5))\" (line 1, column 18)"}

[PAbernet]

I feel like combining different tables in a or or and would work if the parts were indexed from the end. Without having looked at the code, I assume the parsing currently works something like this:

tokens = get_param.split('.')
column = tokens[0]  # table1
operator = tokens[1]  # value1
value = tokens[2]  # gt

Indexing from the end could work like this:

tokens = get_param.split('.')
value = tokens[-1]  # 1
operator = tokens[-2]  # gt
reference = '.'.join(tokens[,-3])  # table1.value1

[wolfgangwalther]

That only seems to work on entire tables, though. Not on columns of joined tables.

$ curl 'http://localhost:3000/table3' -G -d select='*,table1()' -d or='(table1.value1.gt.1,table2.value2.
gt.5)'
{"code":"PGRST100","details":"unknown single value operator value1","hint":null,"message":"\"failed to parse logic tree ((table1.value1.gt.1,table2.value2.gt.5))\" (line 1, column 18)"}

This doesn't make sense.

The question is on which level you want to filter rows out? Do you want to filter child-level rows out, i.e. in the embedding? Then an or condition across multiple tables doesn't make sense.

Do you want to filter top-level rows out? Then use what @steve-chavez suggested above: Make the embedded table contain only "good" rows and then throw the whole top-level row out by checking that "whole table" against null. This also works for two tables with or.

I have a feeling you actually want something else: You want to have the "full, unfiltered embedding" and "filtering the top-level rows with an embedded filter". To achieve this, you need to embed the same table twice: Once to get all the rows in the response - and once to filter on via table filter with the reduced set of rows.