How can I make comparisons in Wordpress SQL wp_postmeta table between columns meta_key and meta

Hello I was wondering how can I make comparisons between the meta_key and meta_value columns found in the wp_postmeta table?

For Example:

Lets assume that I want to select all the post_id's WHERE _wpml_media_featured is equal to 1 and WHERE _stock_status is equal to instock.

I used this code:

SELECT * FROM `Ab64Zf55_postmeta` WHERE 

(`meta_key` = "_wpml_media_featured" AND `meta_value` = 1 ) 

 AND 

(`meta_key` = "_stock_status" AND `meta_value` = "instock" )

But I got 0 results returned from this query!

QUESTION:

What am I doing wrong or how can I modify my code to get what I need?

-Thanks in advance for your help!

CodePudding user response：

you can select all wp_postmeta record with _wpml_media_featured = 1 or _stock_status = instock then group the result by post_id

SELECT post_id FROM Ab64Zf55_postmeta 

WHERE ( meta_key = '_wpml_media_featured' AND meta_value = 1 )

OR ( meta_key = '_stock_status' AND meta_value = 'instock' )

GROUP BY post_id

the above query will give you the list of all the unique post_id

just remember if you select all column (SELECT *), you'll get sql error if only_full_group_by is turned on in your database engine.

Another method is to query the wp_posts table first and separately inner join the two meta fields, this method allows you to pull the record from wp_posts table like post title

SELECT 
    post.ID, 
    post.post_title, 
    wmf.meta_value media_featured, 
    stock.meta_value stock

FROM wp_posts post

INNER JOIN wp_postmeta wmf
    ON  post.ID = wmf.post_id
    AND wmf.meta_key = '_wpml_media_featured'
    AND wmf.meta_value = 1

INNER JOIN wp_postmeta stock
    ON  post.ID = stock.post_id
    AND stock.meta_key = '_stock_status'
    AND stock.meta_value = 'instock'