To fetch the posts I have a query which includes several joins and is quite complicated to use through WP_Query.

SELECT p.post_name, t.name as sname, t2.name as cname FROM wpw0_posts AS p 
INNER JOIN wpw0_term_relationships AS tr ON p.ID = tr.object_id 
INNER JOIN wpw0_term_taxonomy AS tt ON tr.term_taxonomy_id = tt.term_taxonomy_id && tt.taxonomy = 'storylines' 
INNER JOIN wpw0_terms AS t ON t.term_id = tt.term_id 

INNER JOIN wpw0_term_relationships AS tr2 ON p.ID = tr2.object_id 
INNER JOIN wpw0_term_taxonomy AS tt2 ON tr2.term_taxonomy_id = tt2.term_taxonomy_id && tt2.taxonomy = 'company' 
INNER JOIN wpw0_terms AS t2 ON t2.term_id = tt2.term_id 

WHERE p.post_status = 'publish' AND p.post_type = 'post' 
ORDER BY p.post_date DESC

So I fetched the posts using $wpdb object and get_results() method, but the original template has many WP_Query methods like get_the_post_thumbnail_url(), get_the_category(), etc.

So inorder to use the same template I want to convert the array of posts from

$wpdb->get_results($sql);

to

new WP_Query($args);

How can I convert the object or convert the query?

To fetch the posts I have a query which includes several joins and is quite complicated to use through WP_Query.

SELECT p.post_name, t.name as sname, t2.name as cname FROM wpw0_posts AS p 
INNER JOIN wpw0_term_relationships AS tr ON p.ID = tr.object_id 
INNER JOIN wpw0_term_taxonomy AS tt ON tr.term_taxonomy_id = tt.term_taxonomy_id && tt.taxonomy = 'storylines' 
INNER JOIN wpw0_terms AS t ON t.term_id = tt.term_id 

INNER JOIN wpw0_term_relationships AS tr2 ON p.ID = tr2.object_id 
INNER JOIN wpw0_term_taxonomy AS tt2 ON tr2.term_taxonomy_id = tt2.term_taxonomy_id && tt2.taxonomy = 'company' 
INNER JOIN wpw0_terms AS t2 ON t2.term_id = tt2.term_id 

WHERE p.post_status = 'publish' AND p.post_type = 'post' 
ORDER BY p.post_date DESC

So I fetched the posts using $wpdb object and get_results() method, but the original template has many WP_Query methods like get_the_post_thumbnail_url(), get_the_category(), etc.

So inorder to use the same template I want to convert the array of posts from

$wpdb->get_results($sql);

to

new WP_Query($args);

How can I convert the object or convert the query?

• wp-query
• wpdb

• Why do you want to do this instead of just working with the results of your query? – Howdy_McGee ♦ Commented Apr 14, 2020 at 17:55
• Because in order to work with the result, I will have to change the template which will be tedious as its a very big template which uses lots of wp_query methods – Saeesh Tendulkar Commented Apr 14, 2020 at 17:57
• What does your SQL query do? I'm reading it and I see it has something to do with 2 taxonomies, but it's unclear what the goal is. What does it do? – Tom J Nowell ♦ Commented Apr 14, 2020 at 18:06
• @TomJNowell It fetches the posts which compulsorily has the 2 taxonomies added to it. There that simple. – Saeesh Tendulkar Commented Apr 14, 2020 at 18:08
• So, you want a WP_Query that looks for posts, that have at least 2 terms, one that in taxonomy A, and another in taxonomy B. Where the specific term itself is irrelevant, only that it has terms at all? – Tom J Nowell ♦ Commented Apr 14, 2020 at 18:10

1 Answer 1

You won't be able to translate a complicated SQL query directly to a WP_Query. What you can do is return Post IDs from the SQL and pass those IDs into the WP_Query args. Do note that this is highly inefficient and will likely create slow queries as post__in is not fast.

You need to modify the SQL to include the post ID:

SELECT p.ID, p.post_name, /*... etc... */

Next we want to tell wpdb::get_results() to return an array so we can parse out the IDs:

$results = $wpdb->get_results( $sql, ARRAY_A );

Next we want to use wp_list_pluck() to grab the IDs of the results:

$post_ids = wp_list_pluck( $results, 'ID' );

Finally, we can pass these IDs into the posts__in parameter of WP_Query:

$query = new WP_Query( array(
    'post_type'     => 'post',
    'post_status'   => 'publish',
    'post__in'      => $post_ids,
) );

If you also want to get the terms you'll need to either get the term slugs or the term IDs.

You need to modify the SQL to include term IDs. Since you have multiple taxonomies you'll need to alias these differently:

SELECT /* ... etc... */ t.term_id as storyline_term_id, t2.term_id as company_term_id

Next we want to use wpdb::get_results() to return an array so we can parse out the term IDs:

$results = $wpdb->get_results( $sql, ARRAY_A );

Next we want to use wp_list_pluck() to grab the term IDs of the results:

$story_term_ids = wp_list_pluck( $results, 'storyline_term_id' );
$company_term_ids = wp_list_pluck( $results, 'company_term_id' );

Finally, we can get the terms by IDs:

$story_terms = get_terms( array(
    'taxonomy'      => 'storylines',
    'object_ids'    => $story_term_ids,
) );