php - complex and nested WHERE conditions in SQL with LEFT JOIN
Get the solution ↓↓↓
I am trying to fetch posts with multiple filters, my database is as below,
"Meta" Table
"Posts" Table
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 }
Answer
Solution:
You are going to have to check for each condition separately in thehaving clause. Assuming the keys are not repeated, you can dispense with thedistinct and use:
having sum( meta_key in ('location', 'price', 'color') ) = 3 or
sum( meta_key in ('post_type', 'location') ) = 2
Note: You can usecount(distinct) for this, but I don't think it is necessary.
Answer
Solution:
While it does nothing for performance, (I find) it can help to conceptualise this kind of problem by constructing a 'normalised' pivot of the data set, e.g.:
DROP TABLE my_eav;
CREATE TABLE my_eav
(entity INT NOT NULL
,attribute VARCHAR(12) NOT NULL
,value VARCHAR(12) NOT NULL
,PRIMARY KEY(entity,attribute)
);
INSERT INTO my_eav VALUES
(1,'location','new city'),
(1,'post_type','section'),
(1,'price','100'),
(1,'color','red'),
(2,'post_type','product'),
(2,'location','delhi');
SELECT entity
, MAX(CASE WHEN attribute = 'location' THEN value END) location
, MAX(CASE WHEN attribute = 'post_type' THEN value END) post_type
, MAX(CASE WHEN attribute = 'price' THEN value END) price
, MAX(CASE WHEN attribute = 'color' THEN value END) color
FROM my_eav
GROUP
BY entity;
+--------+
Of course, constructing the query this way...
{-code-2}
...allows it to be transformed into a VIEW (e.g. CREATE VIEW eav_view AS...), which can then be updated - one of the few instances where a VIEW is useful...
{-code-3}
Answer
Answer
Answer
Share solution ↓
Additional Information:
Link To Answer People are also looking for solutions of the problem: call to a member function store() on null
Didn't find the answer?
Our community is visited by hundreds of web development professionals every day. Ask your question and get a quick answer for free.
Similar questions
Find the answer in similar questions on our website.
Write quick answer
Do you know the answer to this question? Write a quick response to it. With your help, we will make our community stronger.