How to keep track of multiple OR conditions on Postgres?-CodePudding

I'd like to combine multiple unrelated searches into 1 single query but also be able to add a "reference" to tell apart which records belong to which queries.

Example:

SELECT * FROM users WHERE
(name ILIKE '%mark smith%' AND country='US') // condition id #1
OR
(name ILIKE '%christine smith%') // condition id #2
OR 
...   1000 queries

How can i tell which users match which condition without running a client-side ILIKE simulation on all returned records?

In other words, is there a Postgres pattern to add references to conditions somehow?

condition_1_users = [...]
condition_2_users = [...]
etc..

CodePudding user response：

You can replicate your filtering logic into a CASE expression in the select clause:

SELECT *, CASE WHEN name ILIKE '%mark smith%' AND country = 'US'
               THEN 1
               WHEN name ILIKE '%christine smith%'
               THEN 2
               ... END AS condition
FROM users
WHERE
    (name ILIKE '%mark smith%' AND country = 'US')
    OR
    (name ILIKE '%christine smith%')
    ...