I am trying to fetch posts with multiple filters, my database is as below,
"Meta" Table
| id | meta_key | meta_value | object_name | object_id |
|---|---|---|---|---|
| 2 | location | new city | post | 2 |
| 1 | post_type | section | post | 2 |
"Posts" Table
| id | title | excerpt | description | mime _type | status | parent | post _class | dtime | sort _id | author _id |
|---|---|---|---|---|---|---|---|---|---|---|
| 2 | testing | -1 | post | 2021-04-12 03:06:06 | 0 | 0 | ||||
| 1 | test post | some description | active | -1 | post | 2021-04-12 12:09:32 | 0 | 1 |
SELECT p.id,90p.title FROM posts p LEFT JOIN meta m ON p.id = m.object_id and m.object_name='post' WHERE m.meta_key='location' and m.meta_value='new city' and m.meta_key='post_type' and m.meta_value='section' group by p.idand I tried the above SQL but trouble is I am unable to form right conditional statement, i need to get all the post which have meta key 'post type' with value 'section' and meta key 'location' with value 'new city'.
this query does work with single filter though.
SELECT p.id,90p.title FROM posts p LEFT JOIN meta m ON p.id = m.object_id and m.object_name='post' WHERE m.meta_key='location' and m.meta_value='new city' group by p.id1 Answer
SELECT p.id, p.title
FROM posts p
JOIN meta m ON p.id = m.object_id
WHERE m.object_name='post' AND (m.meta_key, m.meta_value) IN ( ('location', 'new city'), ('post_type', 'section') )
GROUP BY p.id, p.title
HAVING COUNT(DISTINCT m.id) = 2
