How to run a WP_Query on both wp_posts and wp_postmeta simultaneously?

The code here is relevant..
https://codex.www.ads-software.com/Displaying_Posts_Using_a_Custom_Select_Query

Namingly this part…

<?php
	$querystr = "
	SELECT wposts.*
	FROM $wpdb->posts wposts, $wpdb->postmeta wpostmeta
	WHERE wposts.ID = wpostmeta.post_id
	AND wpostmeta.meta_key = 'tag'
	AND wpostmeta.meta_value = 'email'
	AND wposts.post_status = 'publish'
	AND wposts.post_type = 'post'
	AND wposts.post_date < NOW()
	ORDER BY wposts.post_date DESC
	";
	$pageposts = $wpdb->get_results($querystr, OBJECT);
?>

You can use that as a reference to create a joined query, just modify as required..

Didn’t test this but I believe this would also work:

$params=array(
'showposts'=>3,
'post_type' => 'post',
'post_status' => 'publish,pending',
'meta_key'=>'dataX',
'meta_value'=>'Y'
);
$query = new WP_Query;
$results = $query->query($params);

Yes, the built in query system can select based on meta values. Just provide it the meta_key and meta_value in the parameters and it should work.

Cool…. ?? …

Brilliant stuff!

Thanks a million for all your replies.

They are very very very much appreciated!

Nice one ??

MichaelH

I’ve tested your method and it works an absolute treat so that will be what I’m using from now on.

Thank you once again for your help.

??

Thanks a lot MichaelH, it’s very useful.

What if I wanted to select on multiple metakeys?

this doesn’t work:

AND wpostmeta.meta_key = 'keyA'
AND wpostmeta.meta_value = 'ValueA'
AND wpostmeta.meta_key = 'KeyB'
AND wpostmeta.meta_value = 'ValueB'

Any hints? Oh pretty please w sugar on top? ??

$params=array(
'showposts'=>3,
'post_type' => 'post',
'post_status' => 'publish,pending',
'meta_key'=>'dataX',
'meta_value'=>'Y'
);
$query = new WP_Query;
$results = $query->query($params);

This just spits out 3 instances of Y…

I mean.. the same one ??

Hi!

Yes, I’m with the same trouble as seborgarsen, I need to use two meta keys on a query: One to filtering posts and another to sort them.

This is my query so far:

SELECT DISTINCT wposts.*
  FROM wp_posts wposts
  LEFT JOIN wp_postmeta wpostmeta ON wposts.ID = wpostmeta.post_id
  LEFT JOIN wp_term_relationships ON (wposts.ID = wp_term_relationships.object_id)
  LEFT JOIN wp_term_taxonomy ON (wp_term_relationships.term_taxonomy_id = wp_term_taxonomy.term_taxonomy_id)
WHERE wp_term_taxonomy.taxonomy = 'category'
AND wp_term_taxonomy.term_id IN(4,18,19,20,21)
AND wpostmeta.meta_key = 'engine'
AND wpostmeta.meta_value+0 >= 7.5
AND wpostmeta.meta_key = 'weight'
ORDER BY wpostmeta.meta_value+0 desc LIMIT 25

Could I be asking too much?

Thanks in advance!

Hey,

I’m back with the solution for the query involving two custom fields, it was just a matter of a simple sql trick:

SELECT wposts.*
FROM $wpdb->posts wposts, $wpdb->postmeta wpostmeta, $wpdb->postmeta wpostmeta2
WHERE wposts.ID = wpostmeta.post_id
AND wposts.ID = wpostmeta2.post_id
AND wpostmeta.meta_key = 'order'
AND wpostmeta2.meta_key = 'complete'
AND wpostmeta2.meta_value = '1'
AND wposts.post_type = 'post'
AND wposts.post_status = 'publish'
ORDER BY wpostmeta.meta_value ASC

I found it here:
https://kovshenin.com/archives/customize-posts-order-in-wordpress-via-custom-fields/

??

Enjoy!

Great stuff alesub. Thanks for the info ??