CODEWITHSUNDEEP
sqlpostgresqlrelational-division
Solalem
Solalem

Reputation: 13

Find by multiple columns and rows criteria sql query

I have Paths and Criteria tables in PostgreSQL. Paths have multiple criteria that should be fulfilled to be selected.

Path table with (id, name), Criteria table with (id, key, value, path_id)

I need to create an SQL query for selecting a single path that fulfills two or more criteria inputs (for example, age of 30 and male gender). I have tried ANY as given below.

SELECT p.*, array_agg(c.*) as criteria 
FROM paths as p 
INNER JOIN criteria as c ON c.path_id = p.id 
WHERE (c.key, c.value) = ANY (array[
    ("age","30"),
    ("gender","male") 
    ])
GROUP BY p.id

But this gets a path whenever any one of the criteria is met (whether age = 30 or sex = "male" not both). I replaced ANY with ALL but that doesn't return any value at all.

Upvotes: 0

Views: 1446

Answers (2)

user330315
user330315

Reputation:

As you are dealing with key/value pairs, it might be easier to aggregate the criterias into JSON objects and use Postgres' json functions to select those you want:

select p.*, c.*
from paths as p 
  join (
     select path_id, jsonb_object_agg(key, value) as all_criteria
     from criteria
     group by path_id
     having jsonb_object_agg(key, value) @> '{"age": "30", "gender": "male"}'
  )  c on c.path_id = p.id

Online example

Upvotes: 1

GMB
GMB

Reputation: 222482

If I followed you correctly, you need to move the conditions to the having clause instead of the where clause. Also if you want the records that satisfy all conditions, then you need to check for each condition individually:

select p.*, array_agg(c.key || ':' || c.value) as criteria
from paths p
inner join criteria c on c.path_id = p.id
group by p.id
having
    count(*) filter(where c.key = 'age' and c.value = '30') > 0
    and count(*) filter(where c.key = 'gender' and c.value = 'male') > 0

This can also be expressed with two EXISTS conditions, which should be a quit efficient query with an index on criteria(path_id, key, value) (but then you loose the ability to aggregate all the criteria):

select p.*
from paths p
where
    exists (
        select 1 
        from criteria c
        where c.path_id = p.id and c.key = 'age' and c.value = '30'
    )
    and exists (
        select 1 
        from criteria c
        where c.path_id = p.id and c.key = 'gender' and c.value = 'male'
    )

Upvotes: 1

Related Questions