Postgrest: Why aren't "OR" conditions currently supported?

[opensrcery]

It seems like a non-trivial limitation on queries, but perhaps there is a good reason for it. Out of curiosity, why aren't OR conditions supported currently?

From http://postgrest.com/api/reading/#embedding-foreign-entities

For more complicated filters (such as those involving condition 1 OR condition 2) you will have to create a new view in the database.

[begriffs]

Without parentheses it's ambiguous whether "A and B or C" means ((A and B) or C) vs (A and (B or C)).

[steve-chavez]

To avoid ambiguity maybe this could be expressed in a value of the querystring, perhaps something like:

GET /people?where=and(student=is.true,or(age=gte.18,height=lte.180)) 

[begriffs]

It would definitely make postgrest's request grammar richer and avoid the need for as many custom views. Also we are considering possible backward incompatible changes for the next major version of postgrest so this might be a good time to think about ideas for supporting more general boolean logic like you suggest.

What do you think @ruslantalpa, you always have a good instinct for syntax.

[ruslantalpa]

I'm not sure how to feel about this,
On one hand OR is important but to support it you can no longer have each condition be a query parameter, it will all need to be in the same where parameter and it feels like you lose the rest/http feel and move closer to exposing raw sql

[steve-chavez]

I agree on the drawbacks of my previous proposed querystring.

Also I think that the ambiguity problem could be solved since AND has precedence over OR(SELECT FALSE AND FALSE OR TRUE will result in TRUE) and postgrest could just follow that precedence and ignore parentheses precedence.

So maybe this can be adequated in the current postgrest querystring schema in something like:

GET /people?student=is.true&age=gte.18&or.height=lte.180

If the querystring value has an "or." prepended then it will be evaluated with OR, the default querystring value will always be evaluated as AND, and that could be explicit so the above will be the same as:

GET /people?student=is.true&and.age=gte.18&or.height=lte.180

If the first querystring value has a boolean operator it would have to be somehow ignored, also I think that with this way there would be no need for incompatible changes.

[ruslantalpa]

Can you post a few more examples in pairs showing how parantheses can be omited?
Olso note that the order of parameters in the query is not guaranteed

On 06 Jul 2016, at 19:00, Steve Chávez [email protected] wrote:

I agree on the drawbacks of my previous proposed querystring.

Also I think that the ambiguity problem could be solved since AND has precedence over OR(SELECT FALSE AND FALSE OR TRUE will result in TRUE) and postgrest could just follow that precedence and ignore parentheses precedence.

So maybe this can be adequated in the current postgrest querystring schema in something like:

GET /people?student=is.true&age=gte.18&or.height=lte.180
If the querystring value has an "or." prepended then it will be evaluated with OR, the default querystring value will always be evaluated as AND, and that could be explicit so the above will be the same as:

GET /people?student=is.true&and.age=gte.18&or.height=lte.180
—
You are receiving this because you were mentioned.
Reply to this email directly, view it on GitHub, or mute the thread.

[steve-chavez]

My initial thought was that postgrest could just rely on AND precedence, so if for example this query:

SELECT * FROM table WHERE c1 = v1 AND c2 = v2 OR c3 = v3 AND c4 = v4 

Would have to be expressed into postgrest it could be done as:

GET /table?c1=eq.v1&and.c2=eq.v2&or.c3=v3&and.c4=eq.v4

With "and." being optional, but that relied on the wrong belief that querystring preserved order for parameters since most http clients and servers I've worked with preserved that order, but now knowing that there's no RFC that guarantees that I understand it's not an adequate querystring structure for that purpose.

[steve-chavez]

If the querystring keys are not guaranteed to arrive in order maybe the keys could be somehow marked, I thought in something like:

GET /people?1.age=lte.18&2.student=is.true&or.3.height=eq.180&4.weight=gt.30

Where & = AND and &or = OR. Though the querystring gets more complicated.

[ruslantalpa]

prefixing column name is already used to express a filter for a lower level projects.tasks.completed=eq.true so it's not going to work

[opensrcery]

I'm not sure how to feel about this,
On one hand OR is important but to support it you can no longer have each condition be a query parameter, it will all need to be in the same where parameter and it feels like you lose the rest/http feel and move closer to exposing raw sql

@ruslantalpa Would it help to add support for a where parameter (GET /people?where=and(student=is.true,or(age=gte.18,height=lte.180))) while maintaining the current ability to have "each condition be a query parameter?" That way, backwards compatibility would be maintained while adding OR support for those who could benefit from it.

[ruslantalpa]

that would mean two ways of doing the exact same thing (where=and(A,B) and ?A&B) and that's a badly defined interface. What's worse it allows for ambiguous cases like
?A&B&where=and(A,B)

[opensrcery]

I acknowledge the awkwardness of having two ways to do the same thing, but is that not inevitable if we want to eliminate the impedance mismatch between the inherent tree structure of SQL conditions and the flat dictionary of conditions that is currently supported? Attempting to express a SQL condition tree in a flattened form is certainly not impossible, but will inevitably suffer from the same awkwardness of expressing any other kind of tree in this manner. So to allow users to directly express this tree with parens, or not? I am open to elegant solutions in the latter category, but I'm not confident they exist.

To address your specific criticism, in the minority of cases where a power user requires OR queries, and submits an ambiguous query string such as ?A&B&where=and(A,B), these are some options:

• immediately return a 4xx to the user stating that a where parameter cannot be used at the same time as individual filters ?A&B
• process the query while ignoring every filter that is not in the where clause, and rely solely on documentation to surface this behavior to users

I prefer the first option.

[ruslantalpa]

Another thing you have not considered is how do you specify the condition for embedded items. Your where parameter seems to be supporting only the conditions for the top level.
How do you express this
/projects?select=id,name,tasks{id,name}&id=gt.10&tasks.completed=eq.false

(not trying to just criticise, just pointing out the problems i see with the where solution )
also if you are giving the conditions as a string, there is no point in doing age=gt.10, it could just be age > 10 so we slowly get the point of writing sql in urls and that somehow feels wrong, the next thing is going to be a groupby parameter.
There needs to be a limit to the amount of sql features we want to support and defining that limit is the hard part ... i don't know, both things seem important (rest feel and OR support)

[opensrcery]

I see that this is not a trivial decision, nor would the implementation of something like where be trivial.

To the extent that SQL constructs can be expressed as URL parameters, I feel that where, groupby, etc. aren't necessarily "un-RESTful" (though I do see that where feels less RESTful than individual fields as parameters).

Also, there is also the practical matter of the competition postgrest faces from ORMs / fully-featured web frameworks. I would be happy to see this library achieve "effective" feature parity with the popular frameworks, as the concepts behind postgrest are great, and their adoption levels would benefit from the increased real-world use that parity would bring.

[ruslantalpa]

i don't think there is any competition, it's apples and oranges (it's not a library, it's an exacutable/binary) :)

It's not that the parameters are not restful, it's that you are trying to expose the entire sql language to the http and this is not what postgrest is trying to do.
postgrest always generates the same query shape no matter the requests.
if your goal is to have the capabilities of sql over http then it's simpler to just send the raw query in a POST and have a small script proxy it to the db and send back the result.

[opensrcery]

I'm afraid of detracting from the primary issue, so I'll refrain from going further down this detour.

In this event, I think we all agree there is a case to be made for making the interface a bit more complex, to add stronger querying capabilities with OR.

[ruslantalpa]

Anybody else has an idea of how to allow for OR while being backward compatible and loosing the resty feel of the system (although i am afraid @opensrcken is right about this being a tree vs list incompatibility)?

[ruslantalpa]

If there is a backward incompatible change to be made it's probably better to do it now with 4.0 (although nothing is stopping us to have 5.0 for this after 4.1 it it would take to long to implement)

[Trupal00p]

How about something like this?

/projects?select=id,name,tasks{id,name}&or={id=gt.10&tasks.completed=eq.false}

Keeps things relatively flat-ish and is similar to the select parameter.

[Trupal00p]

Another option popped into my mind, replace the "&" with "||" ala javascript:

/projects?select=id,name,tasks{id,name}&id=gt.10||tasks.completed=eq.false

Not sure this would allow nested ORs? may be best to keep more complex queries in views anyway.

[SebAlbert]

@Trupal00p With your first suggestion I strongly dislike the symbols & and = being used "inside" a value of a query-string parameter; they would have to be "urlencoded" (escaped with % followed by two hexadecimal digits), which makes it hard to read. With your second suggestion, you're even technically putting the entire alternative (tasks.completed=eq.false) _inside_ the parameter called "id", which is hardly possible to interpret correctly (i.e. the way you mean it) by the backend.

[begriffs]

Let's try a few combinations using the bracketed or notation and see how it would look.

---

A ∨ B

?A=…&or={B=…}

---

A ∨ B ∨ C

?A=…&or={B=…&or={C=…}}

with a possible syntactic sugar allowing repeated or operators at the top level

?A=…&or={B=…}&or={C=…}

---

A ∨ (B ∧ C)

?A=…&or={B=…&C=…}

---

A ∧ (B ∨ C)
Well here's a problem. We need to somehow parenthesize a term in the conjunction. Solution: give ourselves an and param for this case

?A=…&and={B=…&or={C=…}}

---

(A ∧ B) ∨ C
This one looks a little ambiguous, but we could specify it as

?A=…&B=…&or={C=…}

---

(A ∧ B) ∨ (C ∧ D)

?A=…&B=…&or={C=…&D=…}

---

How do people feel about this notation? It does seem to express everything OK. It is also backward compatible with the old notation, hence concise for the case of typical conjunctive expressions.

There are some other weird interactions though, like what if you put a select clause inside or? Some of our query params are used to shape the result and others are used to filter it. We'd want to enforce that select, order, limit etc say outside of explicit and or or nodes.

We would probably want to get not in there too for a complete set of propositional operators.

[Trupal00p]

I like it in general. SebAlbert raised a good point regarding the & inside of the OR param needing to be urlencoded. I'm not super familiar with how flexible warp is in this regard, would it be able get around this? Perhaps other character(s) would work better inside of and/or nodes?

[ruslantalpa]

@begriffs so far you've described how to specify and+or on the first level. What about the nested conditions?
The example above /projects?select=id,name,tasks{id,name}&or={id=gt.10&tasks.completed=eq.false} is a nonsense since it groups with an or a condition from the first level for id with a condition from a second level tasks.completed, they live in different parts of the query.
All this makes a very awkward way of specifying condition (leading to mistakes, ex. the original suggestion above), you are better off just having an where
The problem here is there are 2 different tree structures to express at the same time
The first one is the and/or conditions, then there is the need to express on which level of the query those conditions are needed?

[steve-chavez]

How about a where parameter that could work like this:

/projects?id=lt.10&id=gt.20&name=like.*son&where=not.(1.or.2).and.3

The numbers represent the filter position as they appear in the querystring, in sql that where will be equal to:

WHERE NOT (id < 10 OR id > 20) AND name LIKE '%son'

Also this where only takes into account non join filters, if there's not a where param then the filters will compare with AND as default so it would be backward compatible.

[ruslantalpa]

@steve-chavez i think the parameter order is not guaranteed so deciding what 1 or 2 means is not trivial and it feels a bit error prone, plus this has to also be able to specify the conditions for deeper levels.

So far i think the where= is the best thing that was mentioned, i.e. if you specify where it takes precedence over normal parameters, or even deny using both in the same query (where and normal parameters).
and the conditions for a deeper level could be level.where= ...
One thing to decide would be if the condition should be like this
where=(column=gt.10) or where=(column > 10) though i fear the second form would raise expectation as to that being a normal sql string and ppl will try crazy stuff there.

Supposing we agree on a interface, there needs to a non trivial refactor to support this thing so it's not going to be soon.

[dcominottim]

Is this still on the roadmap? It is a pretty major use case for some of my projects. Is the current workaround to run 2 (or N) queries instead of 1?

[DanielJoyce]

Need as well. Otherwise docs should be expanded to show how one can add a function rpc endpoint to do something similar. I just need some basic OR support.

[begriffs]

Lots of people want OR queries, I'd say it's one of the frequently asked questions. It's probably a reasonable thing to add, providing great payoff for a modest change in the parser. After reviewing the thread again I appreciate better the beauty of @opensrcken's suggestion of a where query parameter. We already have a select parameter so it's in the same spirit. We can also continue to support the simple implicit-AND filtering when no where param is specified.

I'm leaning toward issuing a 4xx error when both where-style and traditional filtering are used. Also I like the where=(column=gt.10) style rather than where=(column > 10) because it's more consistent with the traditional filtering and looks less like carte blanche sql access.

We should probably use { rather than ( to conform with the select param usage. In hindsight I wish we had chosen parens for select because brackets are considered one of the unsafe characters in RFC 1738:

Other characters are unsafe because gateways and other transport agents are known to sometimes modify such characters. These characters are "{", "}", "|", "\", "^", "~", "[", "]", and "`".

So basically

GET /people?where=and{student=is.true,or{age=gte.18,height=lte.180}}

Any volunteers to work on this? (Assuming nobody objects about the the change.)

@DanielJoyce I added a todo for myself to document the use of a stored proc (or view) as a workaround for OR query support. Something to help a little until the actual feature gets added.

[steve-chavez]

@begriffs And for the join conditions do you agree on /table?relation.where=(..), as proposed in this comment?

[dcominottim]

@begriffs Interesting comment about RFC 1738. I wonder if it has anything to do with why I just couldn't call postgrest at all with the JAX-RS Client (Java) when using brackets in URLs.

[begriffs]

@dcominottim interesting, does it work if you use %7B and %7D rather than { and }? If the brackets really are breaking clients then we should test if parens work better. If so then we can add support for both brackets and parens, but document that brackets are deprecated and remove them in the next major version.

@steve-chavez the level.where= makes sense too. By join conditions you mean the where working alongside resource embedding using nested select, right? Without an appropriate select param I'm imagining that the deeper level where would cause an error.

[dcominottim]

@begriffs Thanks for the tip. Using %7B and %7D, it works. Otherwise, raw brackets produce the following error, no matter if using raw Java Strings or URI objects:

Caused by: java.net.URISyntaxException: Illegal character in query at index 48: http://localhost:8080/rest/films?select=*,actors{*}
    at java.net.URI$Parser.fail(URI.java:2848)
    at java.net.URI$Parser.checkChars(URI.java:3021)
    at java.net.URI$Parser.parseHierarchical(URI.java:3111)
    at java.net.URI$Parser.parse(URI.java:3053)
    at java.net.URI.<init>(URI.java:588)

If we can add support for parens, it would definitely be a great addition.

[steve-chavez]

@begriffs I'll start working on this. If the where is combined with tradional filters I'll raise a 400 Bad Request.

[begriffs]

@steve-chavez thank you so much!

[ruslantalpa]

The idea is that every table (at whatever level) has two virtual columns called and and or
This allows the following:
/people?and={student=is.true,or{age=gte.18,height=lte.180}}
(student=true and (age > 18 or height < 180))

/people?or={student=is.true,and{age=gte.18,height=lte.180}}&driver=eq.true
(student=true or (age > 18 and height < 180)) and driver=true

it works the same for the lower levels, you just use subtable.or and subtable.and
and then inside the {} you don't need to repeat the table name for each column

[ruslantalpa]

About the {} why not change everywhere to () including select. I've seen a lot of issues about people not encoding them and getting errors

[steve-chavez]

An advantage of:

GET /people?or={student=is.true,and{age=gte.18,height=lte.180}}&driver=eq.true

Over the where param is that it can be combined with traditional filters, for not the not.and=.. or not.or=.. can be added.

Also to save another character that needs urlencoding how about removing = from the operators like in:

GET /people?and={student.is.true,or{age.gte.18,height.lte.180}}

[begriffs]

This issue has been resolved, closing.