Bespoke SQL query – accessing records based on 2 (or more) postmeta fields

2 Views

I’m running a site on WordPress and I’m trying to get information from the postmeta table based on 2 (or more) fields. Here is my query so far: 

SELECT wp_postmeta.* FROM wp_postmeta
LEFT JOIN wp_posts ON wp_posts.ID = wp_postmeta.post_id
WHERE wp_posts.post_status = 'publish'
AND wp_posts.post_type = 'post'
AND ( wp_postmeta.meta_key = 'relevantLine' AND wp_postmeta.meta_value = '339' )
AND (
( wp_postmeta.meta_key = 'brandOne' AND wp_postmeta.meta_value = '30' )
OR ( wp_postmeta.meta_key = 'brandTwo' AND wp_postmeta.meta_value = '30' )
OR ( wp_postmeta.meta_key = 'brandThree' AND wp_postmeta.meta_value = '30' )
)
AND wp_posts.post_date >= '2014-03-25'
AND wp_posts.post_date <= '2014-11-27'
GROUP BY wp_posts.ID
ORDER BY wp_posts.post_date DESC

I’m trying to access the records that have the postmeta key “relevantLine” set to 339 AND the postmeta key “brandOne” set to 30 (or “brandTwo” set to 30, or “brandThree” set to 30).

Read More

Does anyone have any idea how to do this?

The above query isn’t working.

Many thanks

PS. I know I could use the wp query functionality but I would like to run the query this way if possible.

Post Views: 2

Related posts

Leave a Reply

1 comment

  1. You can rewrite your as below

    SELECT m.* ,m1.*
FROM wp_postmeta m
JOIN wp_posts p ON p.ID = m.post_id
JOIN wp_postmeta m1 ON p.ID = m1.post_id
WHERE p.post_status = 'publish'
AND p.post_type = 'post'
AND  m.meta_key = 'relevantLine' AND m.meta_value = '339' 
AND m1.meta_key IN ('brandOne','brandThree','brandTwo')
AND m1.meta_value = '30'
AND p.post_date >= '2014-03-25'
AND p.post_date <= '2014-11-27'
GROUP BY p.ID
ORDER BY p.post_date DESC

    This structure is call EAV entity attribute value and for matching between different keys you have to join the table as the different keys you want to compare,I have added only one join to wp_postmeta and for the keys of single value that is 30 i have used IN() to simplify your query.

    Note Using GROUP BY without any aggregate function will give you
    indeterminate results