CODEWITHSUNDEEP
mysqlsqlwordpress
Jeff
Jeff

Reputation: 106

SQL WordPress Custom Query Inner Join

I'm having some difficulty with my SQL statement. I'm doing a query on WordPress to display posts based on multiple post meta fields. When I do the query and filter with only one meta field, or a OR on multiple it works, however AND on multiple fails. 

SELECT wposts . *
FROM wp_posts wposts
INNER JOIN (
  SELECT post_id
  FROM wp_postmeta wpostmeta
  WHERE (
    (wpostmeta.meta_key = 'ulnooweg_business_industry'
      AND wpostmeta.meta_value = 'Legal Services')
    AND (
    wpostmeta.meta_key = 'ulnooweg_business_province'
      AND wpostmeta.meta_value = 'New Brunswick')
  )
  GROUP BY post_id
) 
AS t ON t.post_id = wposts.ID
WHERE wposts.post_status = 'publish'
AND wposts.post_type = 'business'
ORDER BY wposts.post_title ASC
LIMIT 0 , 30

Upvotes: 1

Views: 1800

Answers (3)

xQbert
xQbert

Reputation: 1

The problem is in the where clause of your inner select; I'm guessing wpostmeta returns MULTIPLE rows. A previous comment that a string can't be two values is correct. The 2nd approach should work if the 1st doesn't

at first I thought

WHERE
  ((wpostmeta.meta_key = 'ulnooweg_business_industry' AND wpostmeta.meta_value = 'Legal Services') OR 
   (wpostmeta.meta_key = 'ulnooweg_business_province' AND wpostmeta.meta_value = 'New Brunswick'))
Group by Post_ID
HAVING count(post_ID) = 2

This will work ONLY if there is only one record in the wpostmeta for each type of entry. If postmeta.meta_key = 'ulnooweg_business_industry' AND wpostmeta.meta_value = 'Legal Services' can occur twice, then the above does't work.

2nd approach

Select wposts.*
FROM WP_Posts wposts
INNER JOIN  (
Select POST_ID from WP_POSTMeta where meta_key = 'ulnooweg_business_industry' AND wpostmeta.meta_value = 'Legal Services'
INTERSECT
SELECT POST_ID FROM WP_POST_META WHERE meta_key = 'ulnooweg_business_province' AND wpostmeta.meta_value = 'New Brunswick'
) 
AS T on T.Post_ID = wposts.ID

Upvotes: 0

ghbarratt
ghbarratt

Reputation: 11711

Your query is testing if meta_key (and meta_value) is 2 different values in the same row, which is impossible. But I see what you are trying to do..

Try joining the wp_postmeta table twice except each with an ON clause that excludes all rows except those that satisfy the meta_key condition:

SELECT 
    p.*, 
    GROUP_CONCAT(CONCAT(pm.meta_key,':',pm.meta_value) SEPARATOR ',') AS meta_values
FROM 
    wp_posts p
    JOIN wp_postmeta pm ON pm.post_id = p.ID
    JOIN wp_postmeta pm_bi ON (pm_bi.post_id = p.ID AND pm_bi.meta_key = 'ulnooweg_business_industry')
    JOIN wp_postmeta pm_bp ON (pm_bp.post_id = p.ID AND pm_bp.meta_key = 'ulnooweg_business_province')
WHERE 
    pm_bi.meta_value = 'Legal Services'
    AND pm_bp.meta_value = 'New Brunswick'
    AND p.post_type = 'business'
    AND p.post_status = 'publish'
GROUP BY p.ID
ORDER BY p.post_title ASC

Note: I joined the wp_postmeta table 3 times here to help prove that the conditions are satisfied, but you can remove the GROUP_CONCAT line (and the comma on the previous line of course) and the first JOIN to wp_postmeta and the query will work the same.

Upvotes: 2

Matt Fenwick
Matt Fenwick

Reputation: 49085

In the subquery, it looks like it's looking for records where both wpostmeta.meta_key = 'ulnooweg_business_industry' and wpostmeta.meta_key = 'ulnooweg_business_province' -- in other words, wpostmeta.meta_key needs to be equal to two strings simultaneously to satisfy this condition. Also, it's looking for wpostmeta.meta_value = 'Legal Services' and wpostmeta.meta_value = 'New Brunswick'.

My guess is that this is what you want in the WHERE clause of the subquery -- change one of the ANDs to an OR:

....
WHERE (
  (wpostmeta.meta_key = 'ulnooweg_business_industry'
    AND wpostmeta.meta_value = 'Legal Services')
  OR ( -- changed to an OR
  wpostmeta.meta_key = 'ulnooweg_business_province'
    AND wpostmeta.meta_value = 'New Brunswick')
)
....

Upvotes: 0

Related Questions