Injecting a custom sql query into a page/theme

Question

I have written a custom sql query for our WordPress website (fairly elaborate and not achievable with the standard wordpress code) and I've got it to run via $pageposts = $wpdb->get_results($query, OBJECT);, it yields the results I expect. Now I would like to integrate this query via a hook (ideally), so that I don't have to update the theme page so that we can still update the theme. I know that the hook pre_get_posts() allows you to adjust the main query, but from what I've found it seems to only allow you adjust. E.g. $query->set( 'year', $today['year'] );. I however, want to replace the query completely.

Is this doable using a hook so that I can utilize the main theme code using a hook from my child theme code?

Or will I need to clone the full page from the main theme to the child theme and use the code like this:

<?php 
$pageposts = $wpdb->get_results($q, OBJECT);
if ($pageposts):
    global $post;
    foreach ($pageposts as $post):
        setup_postdata($post);
?>

Answer 1

The hooks you are looking for can be found in the WordPress Codex WP_Query - Filters:

Filters

• posts_distinct - Alters SQL 'DISTINCTROW' clause to the query that returns the post array.
• posts_groupby - Alters SQL 'GROUP BY' clause of the query that returns the post array.
• posts_join - Alters SQL 'JOIN' clause of the query that returns the post array.
• post_limits - Alters SQL 'LIMIT' clause of the query that returns the post array.
• posts_orderby - Alters SQL 'ORDER BY' clause of the query that returns the post array.
• posts_where - Alters SQL 'WHERE' clause of the query that returns the post array.
• posts_join_paged - Alters SQL paging for posts using 'JOIN' clause of the query that returns the post array.
• posts_where_paged - Alters SQL paging for posts using 'WHERE' clause of the query that returns the post array.
• posts_clauses - Alters all the SQL clauses above in one go. It gives you an array of elements that are easy to alter (available with Version 3.1).

Note, that there are more filters than the mentioned. As it is hard to keep the codex up to date, please inspect the get_posts(); function inside the WP_Query class yourself (/wp-includes/query.php).

Firstly, as the Note says, there are more filters than mentioned and, secondly, from the mentioned filters are not all documented. You can do a lot with those included in above list, but it might be necessary to take a look at the source query.php yourself, to find all possibilities you have.

Therefore, without knowing your SQL and your exact plans, I assume you can do what you want by using one or more of those hooks. After you gathered the information you need for your use case take a look around/search here on WPSE for similar implementations, there are plenty of suggested approaches for different scenarios, so you might just find what you need.