Multiple OR clauses in SQL

I am trying to create a query where the user can select over 3 different filters : postal code, municipalities and departments.
The query should return the records where one, two or all of them are chosen for a specific year chosen
The SQL query is as :

select * from db.table
where year = 2021
and (
postal_code =22160
or
municipality = 'Ploumagoar'
or
department = 'Ain'
)

I've tried unsucessfully to do it in metabase using following code:

select * from db.table
    where {{year}} 
    and (
    {{postal_code}}
    or
    {{municipality }}
    or
    {{department }}
    )

The 3 last fields - postal code, municipalities and departments - are not mandatory and should add up as OR conditions.
I've also tried to include the "[[ ]]" conditional formatting but it did not end up as expected.
Can you guide me in order to achieve this goal?

Thanks!

Hi,

What do you want if no filters are added?
I think you should do it like this:

select * from db.table
[[ where {{year}}
and (
{{postal_code}}
or
{{municipality }}
or
{{department }}
) ]]

Thank you for your prompt answer!

If no filters are added, I wish to display all the data, indeed.
I've tried our idea, but the data is not filtered at all.

The more concrete example I can provide, using metabase sample database Orders is:

select * from ORDERS
where 
{{ProductId}}
[[and (
{{discount}}
OR
{{Quantity}})]]

In this example, the ProductId, will indeed filter correctlly the data, but then the last two fields are not taken into account:

The principal difficulty I have is that the three fields are optional but also should be taken into account.

@Charley This should work as you want:

select * from ORDERS
where {{ProductId}}
AND (
    false
    [[OR {{discount}}]]
    [[OR {{Quantity}}]]
)
1 Like

BRILLIANT!!!!
Thank you so much!

Only downside to deal with is that whenever the ProductId is not filled in, no data is displayed. Which is a pain to tackle

@Charley Hacky hack hack

select * from ORDERS
where {{ProductId}}
AND (
    (
      [[false --{{discount}}]] true
      AND
      [[false --{{Quantity}}]] true
    )
    [[OR {{discount}}]]
    [[OR {{Quantity}}]]
)

In your specific case it's possible, but otherwise you are looking for this:
https://github.com/metabase/metabase/issues/13148 - upvote by clicking :+1: on the first post

2 Likes

Oh dear!
This is indeed an Hacky hack hack!
Thanks again @flamber

In the end, due to the field type and some mysterious issues, I had to tweak the code even more!

I am ending up with :

select * from datagouvimmo.foncier where 
{{transactionyear}}
AND (
    (
      coalesce([[{{Commune}},]] true)
      AND
      coalesce([[{{Departement}},]] true)
    )
    [[OR {{Commune}}]]
    [[OR {{Departement}}]]
)

Something that you ,@flamber, mention right here.
So once again: Thank you!