Extending WP_Query with Custom Queries and Tables

Retrieveing data using get_posts or WP_Query can be challenging when we need to include custom tables in the query. In this tutorial, we will learn how to include our custom tables to create custom queries.

The idea for this tutorial came while working on my plugin Simple Sponsorships. I had to retrieve sponsors by packages and also by the content they sponsor.

This plugin has a custom wrapper function ss_get_sponsors( $args = array() ) which basically passes the arguments to the get_posts function.

I could have built new functions but I wanted for me and other developers to have an easier API so I decided to stick only with one function.

Here are two challenges:

1. How to get sponsors by a package when the packages are saved in a custom table,
2. How to get sponsors by the content they sponsor which is also saved in the wp_posts table.

The first challenge should be quite easy to handle since there is a third table sponsorships which has two columns sponsor and package and contains IDs of both. So I had to join this table with the wp_posts table to get the data.

The second challenge was a bit tricky. The content that is sponsored by the Sponsor, has its meta table updated with the meta_key _ss_sponsor and meta_value being the ID of the sponsor. So, if I wanted to get the sponsors back, I had to join another wp_postmeta table and relate it to the sponsor and the content for which I am retreiving it.

WP_Query Filters

Before we go into practical examples, let’s first learn how we can filter the WP_Query.

The function get_posts is also using WP_Query so we need to learn the filters that WP_Query provides for extending the queries. There are a lot of them, so if you want to skip to the examples, you can.

Enable Filters in WP_Query

By default, WP_Query does not take filters into account when creating queries. So, if you want to use those filters, you need to pass in the arguments:

'suppress_filters' => false

How is the Query built

After every filter is passed and the default code is processed, the query is built using variables.

SELECT $found_rows $distinct $fields FROM {$wpdb->posts} $join WHERE 1=1 $where $groupby $orderby $limits

The variable $found_rows can’t be filtered. This variable will hold SQL_CALC_FOUND_ROWS which is used when we need pagination. If we set the argument no_found_rows to true, this won’t be used (which makes a faster query). The function get_posts() does that by default.

Filtering DISTINCT in WP_Query

To filter the variable $distinct, we can use two filters. The second filter is intended for caching plugins so we can focus on one only (unless you’re building a caching plugin). They only differ in the filter name.

The filters are:

• posts_distinct
• posts_distinct_request

The parameters that are passed in those filters are $distinct (string) and the WP_Query object. The object is passed by reference (any change to the object will remain also outside the method/function) .

add_filter( 'posts_distinct', 'some_function_distinct', 10, 2 );

/**
 * We will return the 'DISTINCT'.
 *
 * @param string $distinct String containing DISTINCT or empty.
 * @param WP_Query $wp_query Object.
 * @return string
 */
function some_function_distinct( $distinct, $wp_query ) {
 return 'DISTINCT';
}

Filtering the Fields in WP_Query

The variable $fields is used for the fields that the query will return. In my case, described above, I did not have to filter those, but if do, you can have it return fields from other tables as well. By default, the $fields will always return the fields from the wp_posts table.

Filters:

• posts_fields
• posts_fields_request (for caching plugins)

add_filter( 'posts_fields', 'only_title_field', 10, 2 );

/**
 * We want only the title of the posts
 *
 * @param string $fields String containing fields.
 * @param WP_Query $wp_query Object.
 * @return string
 */
function only_title_field( $fields, $wp_query ) {
 global $wpdb;
 $fields = "{$wpdb->posts}.post_title";
 return $fields;
}

Filtering the JOIN tables in WP_Query

When filtering the $join variable, we need to be sure to only add our joins there without removing anything else. Otherwise, if we don’t know what we’re doing, we could break the whole query.

Filters:

• posts_join,
• posts_join_paged – for manipulating paging queries,
• posts_join_request – for caching plugins.

add_filter( 'posts_join', 'add_other_table', 10, 2 );

/**
 * Joining another table and relating the column post_id with the Post's ID
 *
 * @param string $join String containing joins.
 * @param WP_Query $wp_query Object.
 * @return string
 */
function add_other_table( $join, $wp_query ) {
 global $wpdb;
 $join .= " JOIN {$wpdb->prefix}my_table as mytable on mytable.post_id = {$wpdb->posts}.ID ";
 return $join;
}

Filtering the WHERE in WP_Query

When filtering the $where variable we must be sure that we have the columns and tables available. When we filter the $where with some custom columns, we will most certainly have to also filter the $join variable as we did above.

Filters:

• posts_where,
• posts_where_paged – for manipulating paging queries,
• posts_where_request – for caching plugins.

add_filter( 'posts_where', 'where_other_table', 10, 2 );

/**
 * Filtering only the posts with a share count above 10
 *
 * @param string $where String containing where clauses.
 * @param WP_Query $wp_query Object.
 * @return string
 */
function where_other_table( $where, $wp_query ) {
 $where .= " AND mytable.share_count > 10 ";
 return $where;
}

Filtering the GROUP BY in WP_Query

To group by an SQL result, the column used in the group by must be also available in the filtered $fields.

Filters:

• posts_groupby,
• posts_groupby_request – for caching plugins.

add_filter( 'posts_groupby', 'groupby_other_table', 10, 2 );

/**
 * We will first order by the share count.
 *
 * @param string $groupby String containing groupby fields.
 * @param WP_Query $wp_query Object.
 * @return string
 */
function orderby_other_table( $groupby, $wp_query ) {
 $comma   = "";
 if ( $groupby ) {
  $comma = ", ";
 }
 $groupby = "posts.post_title" . $comma . $groupby;
 return $groupby;
}

Filtering the ORDER BY in WP_Query

When you want to order the query by some parameter, you have to use the columns from the tables.

Filters:

• posts_orderby,
• posts_orderby_request – for caching plugins.

add_filter( 'posts_orderby', 'orderby_other_table', 10, 2 );

/**
 * We will first order by the share count.
 *
 * @param string $orderby String containing orderby fields.
 * @param WP_Query $wp_query Object.
 * @return string
 */
function orderby_other_table( $orderby, $wp_query ) {
 $comma   = "";
 if ( $orderby ) {
  $comma = ", ";
 }
 $orderby = "mytable.share_count" . $comma . $orderby;
 return $orderby;
}

Filtering the LIMIT in WP_Query

The LIMIT is used to limit the number of results returned by the query. This is useful when creating paginated results. It also accepts two parameters in SQL query. If 2 parameters are used, the first one would be the offset while the second one would be the number of rows; example: LIMIT 0, 10 (First ten records).

Filters:

• post_limits,
• post_limits_request – for caching plugins.

add_filter( 'post_limits', 'limit_sql', 10, 2 );

/**
 * Limiting the Query.
 *
 * @param string $limit String containing the limit clause.
 * @param WP_Query $wp_query Object.
 * @return string
 */
function limit_sql( $limit, $wp_query ) {
 return "LIMIT 10";
}

Filtering every part in WP_Query

If you don’t want to remember or use filters for separate parts, you can use another filter that will pass all of the above filtered variables into an array.

Filters:

• posts_clauses,
• posts_clauses_request – for caching plugins.

Each clause is available in this array with the same name as the variable before.

Other Interesting Filters in WP_Query

There are some interesting filters which you might find helpful:

• posts_request – Filters the completed SQL statement,
• posts_pre_query – If this filter returns an array of posts, it will bypass the whole querying. By default, it returns null,
• posts_results – Filters the raw posts result array,
• the_preview – Filters the WP_Post object when previewing,
• the_posts – Filters the retrieved array of WP_Post objects,
• posts_search – Filters the Search SQL,
• wp_search_stopwords – Filters an array of stopwords that are excluded from search,
• posts_search_orderby – Filters the ORDER BY used when searching,
• wp_query_search_exclusion_prefix – Filters the prefix which, if a term has, excludes them from search.

Practical Examples of Extending the WP_Query

Let’s now go over the challenges that I’ve described at the beginning of this article.

I’ve extended the $wpdb with my own table names:

• $wpdb->ss_sponsorships = $wpdb->prefix . 'sssponsorships'

Filtering WP_Query by a Custom Table

In my scenario, the custom table was $wpdb->sssponsorships. I had to join this table using the filter posts_join and the filter posts_where to filter them out.

I am using INNER JOIN here so if no sponsor has a sponsorship here, it will return no sponsors. I am also checking the $wp_query->query parameters to be sure I am requesting sponsors for a package. With this join, we have made sure that the row in the posts table is related to all the sponsorships with that ID in the column sponsor.

Now we need to tackle the where part.

In the where clause, I am filtering the sponsors that have the provided package in the query arguments.

Since there can be more than 1 sponsorship from a sponsor and with the same package, I had to use the $distinct part to return DISTINCT as we did in the example in this tutorial.

Filtering WP_Query by the same table

In the second challenge, where I had to return sponsors that are sponsoring a post, page or any custom post types, I had to relate them to the postmeta table.

The issue here is that we can add a sponsor to any available post type. Because of that, we have sponsors that are a CPT and the content which is also a CPT (or post/page). We store the sponsor IDs to the postmeta information of the content.

So, how can we use the function get_posts() and retrieve sponsors? To do that, we can’t use the standard way of retrieving posts by meta keys since then, we would retrieve the content and not the sponsors.

To retrieve sponsors by content, we will pass the argument ss_content in the get_posts(). So we will have to check against that argument.

So, what are we doing here? We are making a new JOIN with the postmeta table because this one will be related to our sponsor ID (in wp_posts table).

But in this case, we will relate the meta_value column to the ID instead of the post_id column. Why? Because that’s where we store the sponsor IDs to other content. For this joined table, the post_id column will contain the ID of the content. Let’s now do the where clause and connect that.

Here, we are connecting that by checking the provided content ID (ss_content) against the column post_id of our newly joined table and make sure that the meta_key is _ss_sponsor.

Creating the wrapper for your own plugins

Let’s now learn how you can create your own function that could be used to wrap that and be used by you and other developers.

Conclusion

WP_Query is a powerful class and provides lots of filters and actions which you can use to change how the WordPress loop displays the data and how the query retrieves the data.

By extending the WP_Query class, you can provide a new API for your plugin with minor improvements.

Become a Sponsor