Use WP_Query with a custom SQL query

Question

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?

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,
) );