I am trying to fetch posts with multiple filters, my database is as below,
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
I tried the above SQL and it works but trouble is if i nest more than two OR conditions with different numbers of filters i am getting wrong results.
just for explaining what i want , not sql :
SELECT p.id, p.title
FROM posts p
JOIN meta m ON p.id = m.object_id
WHERE m.object_name='post'
AND
(
#SET 1 = (
(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
#SET 2 = (
m.meta_key = 'post_type' and m.meta_value = 'product'
OR m.meta_key = 'location' and m.meta_value = 'delhi'
)
)
AND p.post_class='post'
GROUP BY p.id, p.title
HAVING if #SET 1 matched { COUNT(DISTINCT m.id) = 3 } elseIf #SET 2 matched {
COUNT(DISTINCT m.id) = 2 }
The following recommendations will help you in your SQL tuning process.
You'll find 3 sections below:
ALTER TABLE `meta` ADD INDEX `meta_idx_object_name` (`object_name`);
ALTER TABLE `posts` ADD INDEX `posts_idx_id_title` (`id`,`title`);
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
ORDER BY
NULL