How to extend WP_Query to include custom table in query?

by

Important disclaimer: the proper way to do this is NOT to modify your
table structure, but to use wp_usermeta. Then you will not need to
create any custom SQL to query your posts (though you’ll still need
some custom SQL to get a list of everyone that reports to a particular
supervisor – in the Admin section, for instance). However, since the
OP asked about writing custom SQL, here is the current best practice
for injecting custom SQL into an existing WordPress Query.

If you’re doing complex joins, you can’t just use the posts_where filter, because you’ll need to modify the join, the select, and possibly the group by or order by sections of the query as well.

Your best bet is to use the ‘posts_clauses’ filter. This is a highly useful filter (that shouldn’t be abused!) that allows you to append / modify the various portions of the SQL that is generated automatically by the many many lines of code within WordPress core. The filter callback signature is:
function posts_clauses_filter_cb( $clauses, $query_object ){ } and it expects you to return $clauses.

The Clauses

$clauses is an array that contains the following keys; each key is an SQL string that will be directly used in the final SQL statement sent to the database:

  • where
  • groupby
  • join
  • orderby
  • distinct
  • fields
  • limits

If you’re adding a table to the database (only do this if you absolutely can’t leverage post_meta, user_meta or taxonomies) you’ll probably need to touch more than one of these clauses, for example, the fields (the “SELECT” portion of the SQL statement), the join (all your tables, other than the one in your “FROM” clause), and maybe the orderby.

Modifying the Clauses

The best way to do this is to subreference the relevant key from the $clauses array you got from the filter:

$join = &$clauses['join'];

Now, if you modify $join, you’ll actually be directly modifying $clauses['join'] so the changes will be in $clauses when you return it.

Preserving the Original Clauses

Chances are (no, seriously, listen up) you will want to preserve the existing SQL that WordPress generated for you. If not, you should probably look at the posts_request filter instead – that is the complete mySQL query just before it’s sent off to the database, so you can totally clobber it with your own. Why would you want to do this? You probably don’t.

So, in order to preserve the existing SQL in the clauses, remember to append to the clauses, not assign to them (ie: use $join .= ' {NEW SQL STUFF}'; not $join = '{CLOBBER SQL STUFF}';. Note that because each element of the $clauses array is a string, if you want to append to it, you’ll probably want to insert a space before any other character tokens, otherwise you’ll probably create some SQL syntax error.

You can just assume there will always be something in each of the clauses, and so remember to start each new string with a space, as in: $join .= ' my_table, or, you can always add a little line that only adds a space if you need to:

$join = &$clauses['join'];
if (! empty( $join ) ) $join .= ' ';
$join .= "JOIN my_table... "; // <-- note the space at the end
$join .= "JOIN my_other_table... ";


return $clauses;

That’s a stylistic thing more than anything else. The important bit to remember is: always leave a space BEFORE your string if you’re appending to a clause that already has some SQL in it!

Putting it together

The first rule of WordPress development is to try to use as much core functionality as you can. This is the best way to future proof your work. Suppose the core team decides that WordPress will now be using SQLite or Oracle or some other database language. Any hand-written mySQL may become invalid and break your plugin or theme! Better to let WP generate as much SQL as possible on its own, and just add the bits you need.

So first order of business is leveraging WP_Query to generate as much of your base query as possible. The exact method we use to do this depends largely on where this list of posts is supposed to appear. If it’s a sub-section of the page (not your main query) you would use get_posts(); if it’s the main query, I suppose you could use query_posts() and be done with it, but the proper way to do it is to intercept the main query before it hits the database (and consumes server cycles) so use the request filter.

Okay, so you’ve generated your query and the SQL is about to be created. Well, in fact, it has been created, just not sent to the database. By using the posts_clauses filter, you’re going to add your employee relationships table into the mix. Let’s call this table {$wpdb->prefix} . ‘user_relationship’, and it’s an intersection table. (By the way, I recommend that you genericize this table structure and turn it into a proper intersection table with the following fields: ‘relationship_id’, ‘user_id’, ‘related_user_id’, ‘relationship_type’; this is much more flexible and powerful… but I digress).

If I understand what you want to do, you want to pass a Leader’s ID and then see only the posts by that Leader’s Followers. I hope I got that right. If it’s not right, you’ll have to take what I say and adapt it to your needs. I’ll stick with your table structure: we have a leader_id and a follower_id. So the JOIN will be on {$wpdb->posts}.post_author as a foreign key to the ‘follower_id’ on your ‘user_relationship’ table.

add_filter( 'posts_clauses', 'filter_by_leader_id', 10, 2 ); // we need the 2 because we want to get all the arguments

function filter_by_leader_id( $clauses, $query_object ){
  // I don't know how you intend to pass the leader_id, so let's just assume it's a global
  global $leader_id;

  // In this example I only want to affect a query on the home page.
  // This is where the $query_object is used, to help us avoid affecting
  // ALL queries (since ALL queries pass through this filter)
  if ( $query_object->is_home() ){
    // Now, let's add your table into the SQL
    $join = &$clauses['join'];
    if (! empty( $join ) ) $join .= ' '; // add a space only if we have to (for bonus marks!)
    $join .= "JOIN {$wpdb->prefix}employee_relationship EMP_R ON EMP_R.follower_id = {$wpdb->posts}.author_id";

    // And make sure we add it to our selection criteria
    $where = &$clauses['where'];
    // Regardless, you always start with AND, because there's always a '1=1' statement as the first statement of the WHERE clause that's added in by WP/
    // Just don't forget the leading space!
    $where .= " AND EMP_R.leader_id={$leader_id}"; // assuming $leader_id is always (int)

    // And I assume you'll want the posts "grouped" by user id, so let's modify the groupby clause
    $groupby = &$clauses['groupby'];
    // We need to prepend, so...
    if (! empty( $groupby ) ) $groupby = ' ' . $groupby; // For the show-offs
    $groupby = "{$wpdb->posts}.post_author" . $groupby;
  }

  // Regardless, we need to return our clauses...
  return $clauses;
}

Leave a Comment