search for multiple values with LEFT JOINing several tables in MySQL

Question

I have a big problem trying to search for multiple values in MySQL with LEFT JOINing several tables and doing an "all search" with possible extra parameters to filter on. Allow me to elaborate an example case:

I have the following DB structure:

wp_posts
id | post_name
--------------
1  | sword
2  | bow
3  | shield

wp_postmeta
id  | post_id   | meta_key  | meta_value
------------------------------------
1   |   1       |   user    | 101
2   |   1       |   power   | 5
3   |   1       |   defense | 0
4   |   1       |   range   | 1
5   |   1       |   condi   | old
6   |   2       |   user    | 102
7   |   2       |   range   | 10
8   |   2       |   condi   | new
9   |   3       |   user    | 101
10  |   3       |   power   | 0
11  |   3       |   defense | 10

I'm building a PHP MySQL search with the following:

1) an "all search" input. Search for any post_name or any meta_value. 2) a filter input to choose the meta_key and input the meta_value

E.g. search for "sword" filter on power = '5'

SELECT *
FROM wp_posts as p
LEFT JOIN wp_postmeta as pm ON p.id = pm.post_id
WHERE (post_name LIKE '%$search_str%' OR meta_value LIKE '%$search_str%')
/* extra filter */
AND (meta_key = '$filter_key' AND meta_value = '$filter_val')
GROUP BY p.id

MY PROBLEM:

I always want 1 result per record in wp_posts. But because of my LEFT JOIN every post_key and post_value will result as a different row.
So I get nothing if I search for things with multiple filters (on multiple meta_values).

e.g. search for old in all search + filter on range = 1.

SELECT * FROM wp_posts as p
LEFT JOIN wp_postmeta as pm ON p.id = pm.post_id
WHERE (post_name LIKE '%old%' OR meta_value LIKE '%old%')
AND (meta_key = 'range' AND meta_value LIKE '%1%')
GROUP BY p.id

→　0 results...

So I know the reason but I don't know a good workaround...
I know I can e.g. group concat columns, but then I cannot keep the meta_key and meta_value as sets right? Any ideas on a good way to do this?

(wait until you hear the problem I'm having adding a 3rd table with tags and adding that to my all-search box.......)

Answer 1

I would use an exists subquery to get around this problem:

SELECT * FROM wp_posts as p
LEFT JOIN wp_postmeta as pm ON p.id = pm.post_id
WHERE (post_name LIKE '%old%' OR meta_value LIKE '%old%')
AND EXISTS (SELECT 1 FROM wp_postmeta pm2 WHERE pm2.meta_key = 'range' AND pm2.meta_value LIKE '%1%' and pm2.post_id=pm.post_id)
GROUP BY p.id

But be aware, that your query depends on the full group by sql mode not being set. This sql mode is now on by default in the newer versions of mysql.