John Davidson

Message:

following is db fiddle for tables and query.

https://www.db-fiddle.com/f/wcgBNqmhcfeKREAo3XyvtZ/3

META

POSTS

TERMS_RELATIONSHIP

i want to select post ID from posts based on meta data it has and terms connection it has
but somehow it is behaving abruptly,
as when i pass

AND(tr.term_id = 1 or tr.term_id = 2)



.....

HAVING ....OR CONDITIONS ... AND (sum(tr.term_id in (1,2)) = 2)

it works fine but when i pass

AND(tr.term_id = 3 or tr.term_id = 4)



.....

HAVING ....OR CONDITIONS ... AND (sum(tr.term_id in (3,4)) = 2)

it doesn't work and return no rows.
i want to check if both term_id exists for a given post AND either of the meta condition set exists.

SELECT p.id

FROM posts p

JOIN meta m ON p.id = m.object_id

JOIN terms_relationship tr ON p.id = tr.object_id

WHERE

m.object_name='post' and tr.relationship_type = 'post-term'

    

-- META QUERY;

    

AND(

(m.meta_key = 'location' and m.meta_value = 'new city')

or (m.meta_key = 'price' and m.meta_value = 100)

or (m.meta_key = 'color' and m.meta_value = 'red')

)

OR(

(m.meta_key = 'location' and m.meta_value = 'delhi')

or (m.meta_key = 'price' and m.meta_value = 100))

    

-- TERM QUERY;

    

AND(

tr.term_id = 1 or tr.term_id = 2

)

    

GROUP BY p.id

    

having (sum( meta_key in ('location', 'price', 'color')) = 3

OR sum( meta_key in ('post_type', 'location')) = 2)

AND (sum(tr.term_id in (1,2)) = 2)

---

My Answer:
The issue you are facing is due to the way you are checking for the existence of both term_ids in the HAVING clause. When you use `sum(tr.term_id in (3,4)) = 2`, it is checking if the sum of boolean values (0 or 1) for term_ids 3 and 4 is equal to 2, which will never be true.

Instead, you can modify your query to check for the existence of both term_ids using a combination of SUM and CASE statements. Here's an updated version of your query:

sql
SELECT p.id
FROM posts p
JOIN meta m ON p.id = m.object_id
JOIN terms_relationship tr ON p.id = tr.object_id
WHERE m.object_name='post' and tr.relationship_type = 'post-term'
AND (
(m.meta_key = 'location' and m.meta_value = 'new city')
OR (m.meta_key = 'price' and m.meta_value = 100)
OR (m.meta_key = 'color' and m.meta_value = 'red')
OR (m.meta_key = 'location' and m.meta_value = 'delhi')
OR (m.meta_key = 'price' and m.meta_value = 100)
)
AND (tr.term_id = 3 or tr.term_id = 4)
GROUP BY p.id
HAVING
(sum( meta_key in ('location', 'price', 'color')) = 3 OR sum( meta_key in ('post_type', 'location')) = 2)
AND (sum(CASE WHEN tr.term_id = 3 THEN 1 ELSE 0 END) > 0 AND sum(CASE WHEN tr.term_id = 4 THEN 1 ELSE 0 END) > 0);


This query will check for the existence of both term_ids 3 and 4 for a given post, along with the specified meta conditions.