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.
Rate this post
4 of 5 based on 7137 votesComments