How to combine multiple OR conditions

[arnaud-thorel-of]

Hello there,

I'm currently writing a QueryDSL annotated based librairy that allow to simplifie querying postgrest in java

Could you tell me how to write a simple case that contains multiple or on multiple variables ?

My Case :
I have to retrieves data with valide_date and valid_status

StartDate => $SearchDate & EndDate is null or EndDate <= $SearchDate & StatusCode is Null or StatusCode is in ($SearchStatus)

I understant according to the documentation that I have to write something like this
posts?startDate=lte.$searchDate&or=(endDate.is.null, endDate.gte.$searchDate) <= But when i have a second or like or=(status.is.null, status.in.(value1,value2) I can't figure how to write it

Have you ever have to do this, and how.
Maybe it should be explained in the documentation.

Best regards

[laurenceisla]

Hi. I suggest converting your query to SQL first. I'm assuming this the filter you're looking for:

WHERE 
  (StartDate => $SearchDate AND EndDate IS NULL) 
  OR (EndDate <= $SearchDate AND StatusCode IS NULL)
  OR (StatusCode IS IN ($SearchStatus))

The or operator will apply an OR to all the elements inside, e.g. this:

?or=(val1.is.null,val2.is,null)

translates to

WHERE (val1 IS NULL OR val2 IS NULL)

So, to simplify your query, it could be expressed like this:

?or=(condition1,condition2,condition3)

Since these conditions have an AND between them, then you'll have to use the and() operator inside (I added spacing it so you can see it better):

?or=(
  and(startDate=gte.$searchDate,endDate.is.null),
  and(endDate.lte.$searchDate,StatusCode.is.null),
  StatusCode.in.(value1,value2)
)

You can guide yourself with that example. If your query is different then express it in SQL, please.

[arnaud-thorel-of]

Ok looks like it's much more this condition. I will give it a try

?startDate.lte.2023-10-10 // should be less than
&and=(
or(endDate.gte.2023-10-10, endDate.is.null), // and should be null or gte
or(statusCode.is.null, statusCode.in.(code1,code2)) // and should be null or in list (code1, code2)
)

so, if i have multiple or I have to wrap it inside a andCondition

[laurenceisla]

I'm still a bit confused since it's not a SQL query. The and is not needed if it's not inside another and or or. For instance, this ?and=(val1.eq.1,val2.eq.2) is the same as ?val1=eq.1&val2=eq.2, so your query could be written as

?startDate.lte.2023-10-10 // should be less than
&or(endDate.gte.2023-10-10, endDate.is.null), // and should be null or gte
&or(statusCode.is.null, statusCode.in.(code1,code2)) // and should be null or in list (code1, code2)

[arnaud-thorel-of]

I'll give it a try, but i'm currently writing a java library and passing arguments are in HashMap<String, String>
so I can't have two "or" keys as the same i'm not sure that you can write querystrings with the same identifier

&or="cond1"&or="cond2"

[laurenceisla]

Ah, I see. Take into consideration that PostgREST allows using the same identifier. For example, this is useful when you need to do something like ?price=gte.10&price=lt.50.

[arnaud-thorel-of]

Thx Laurence,

I made some research by my side and see that webclient java implementation allow to multimapValues

{
startDate=[lte.2023-11-10],
or=[(endDate.gte.2023-11-10,endDate.is.null), (status.is.null, status.in(a,b,c)],
status=not.in.(z)
}

[arnaud-thorel-of]

Answer by @laurenceisla

Postgrest accept multiple key params, so you can write ?price=gte.10&price=lt.50 and it will be execute as (PRICE >= 10 AND price <= 50)

It works also with complexe fonctions (or/not)