mitcho Michael 芳貴 Erlewine

Postdoctoral fellow, McGill Linguistics.

blog

External orders in WordPress queries

The advanced WordPress user is intimately familiar with query_posts, the function which controls which posts are displayed in “The Loop.” query_posts gives plugin and theme writers the ability to display only posts written in Janary (query_posts("monthnum=1")) or disallow posts from a certain category (query_posts("cat=-529")1). One of the parameters you can set here is orderby which affects the ordering of the posts returned, with allowed values such as author, date, or title. But what if you want to order your posts in some other order, defined outside of your wp_posts table? Here I’m going to lay out some thoughts on rolling your own external ordering source for WordPress queries.

In order to introduce an external ordering source, we need to do four things: 1. create the external ordering source, 2. hook up (read “join”) the external ordering source 3. make sure we use that order, and 4. make it play nice. ^^

By the way, I’m going to assume you, dear reader, are PHP-savvy, proficient in MySQL, and already know a little about WordPress. This how-to is not for the PHPhobic.

The ordering source

For this example, suppose we want to display posts by order of “interestingness.” We’ll just create a table called wp_interestingness with two columns, ID and interestingness and populate it with some data. We’ll even be nice to our database by making sure the ID is the primary key. Easy.

Hook up the external ordering source

When you run a query through query_posts() (or use WP_Query’s query method2), what it’s doing is taking your special request and translating it into a MySQL statement. This means a query like "monthnum=1" is turned into SELECT ... wp_posts.* FROM wp_posts WHERE 1=1 AND MONTH(wp_posts.post_date)='1' .... Every different query introduces something new to the basic SELECT command—in this case, the AND MONTH(wp_posts.post_date)='1'.

We first want to introduce the interestingness for each post and that means joining the new table into the query. We’ll do this using the posts_join filter. This filter lets you add a join statement to the MySQL request.

  1. add_filter('posts_join','my_join_filter');
  2. function my_join_filter($arg) {
  3. 	$arg .= " natural join wp_interestingness ";
  4. 	return $arg;
  5. }

Note that here we’re using natural join as wp_posts and wp_interestingness have only one key in common, ID, and that’s exactly the column we want to join them on.

Use the new order

Now that we’ve joined wp_interestingness in, we can refer to wp_interestingness.interestingness in our query. Note now that, by default, an $wpdb->posts.post_date will be used to order the posts. We’ll use another filter here; this time posts_orderby, to patch this part of the query. We’ll search for the default ORDER BY value and replace it with our own interestingness.

  1. add_filter('posts_orderby','my_orderby_filter');
  2. function my_orderby_filter($arg) {
  3. 	global $wpdb;
  4. 	$arg = str_replace("$wpdb->posts.post_date","wp_interestingness.interestingness",$arg);
  5. 	return $arg;
  6. }

By the way, you can now check the resulting MySQL query by echoing $wp_query->request. (If you’re using the WP_Query method I advocated below in footnote (2), you’ll of course have to change $wp_query to the WP_Query object you’re using.)

Learn to play nice ^^

The instructions above do indeed work, but they also cause some major breakdowns in other functions of your blog. Why? That’s because the current code will edit your queries for every instance of The Loop: your index page, your archives, and your RSS feeds. You probably only want to search by interestingness in certain situations. What we need is a way to tell our (admittedly stupid) my_join_filter and my_orderby_filter when they should apply their interestingness magic and when they shouldn’t. There are several ways to set up such a system but here I’ll lay out one that I feel is particularly elegant. We’ll set it up so you can actually use query_posts("orderby=interestingness") and it’ll know what you’re talking about.

One of the first things that happens in query_posts—indeed, way before even the posts_join and posts_orderby filters—is an action hook called parse_query. This lets us look at the initial state of the WP_Query object as it starts to run. In particular, we can look at the orderby query variable and see if we want to order by interestingness. If we do, we’ll set a global variable called $use_interestingness_flag to be true.

  1. add_action('parse_query','set_use_interestingness_flag');
  2. function set_use_interestingness_flag($query) {
  3. 	global $use_interestingness_flag;
  4. 	if ($query->query_vars['orderby'] == 'interestingness')
  5. 		$yarpp_score_override = true;
  6. 	else
  7. 		$yarpp_score_override = false;
  8. }

Now we just have to edit our filters so they only run when $use_interestingness_flag == true. We also will make sure to turn the flag back off in my_orderby_filter, as it’s our last filter to run during each query. It’s just like putting the seat back down after using a unisex bathroom.3

  1. add_filter('posts_join','my_join_filter');
  2. function my_join_filter($arg) {
  3. 	global $use_interestingness_flag;
  4. 	if ($use_interestingness_flag)
  5. 		$arg .= " natural join wp_interestingness ";
  6. 	return $arg;
  7. }
  8. add_filter('posts_orderby','my_orderby_filter');
  9. function my_orderby_filter($arg) {
  10. 	global $wpdb, $use_interestingness_flag;
  11. 	if ($use_interestingness_flag)
  12. 		$arg = str_replace("$wpdb->posts.post_date","wp_interestingness.interestingness",$arg);
  13. 	$use_interestingness_flag = false;
  14. 	return $arg;
  15. }

This method has a great advantage as you can just set it up once and invoke it whenever you want, even together with other parameters, without any additional code. For example, you can try query_posts("monthnum=1&orderby=interestingness") or query_posts("cat=-529&orderby=interestingness").

Conclusion

Adding an external ordering source to your WordPress post queries can be relatively straightforward if you understand what query_posts does and take advantage of its hooks. This tutorial can also serve as the basis for many other patches to WP_Query, not just the orderby parameter. To better understand the way WordPress builds its MySQL queries and the many posts_* filters which you can take advantage of, go to the source: wp-includes/query.php. Finally, you can use the special parse_query hook and global variables as flags to only apply the filters when necessary.


  1. This, incidentally, is precisely what I do to hide, by default, my tweets in my index.php and archives.php

  2. If you’re going to get serious about rolling your WordPress queries I highly recommend you follow Mark Ghosh’s advice on initializing another object of the WP_Query class and using the query method, rather than just using the global query_posts function. 

  3. The perceptive reader will note that we are still searching for the string "$wpdb->posts.post_date" in my_orderby_filter, instead of something like "$wpdb->posts.interestingness". That’s because the orderby value of interestingness is not one of the allowed orderby values (search for $allowed_keys in wp-includes/query.php to see the list). Thus the MySQL ORDER BY value is set to the default of "$wpdb->posts.post_date" before it gets to the posts_orderby filter. Now you know. 

Tags: , , , , , , , , , , , , , ,

If you enjoyed this post, make sure you subscribe to my RSS feed (optionally with tweets from my Twitter)!

5 Responses to “External orders in WordPress queries”

  1. mitcho > blog > Using Templates with YARPP 3 Says:

    […] For those of you interested in the WP and SQL voodoo used to make this happen, I’ve posted a more technical article. ↩ […]

  2. Joe Says:

    Hi Mitcho,

    First off — Hats off to the work, this is a great plugin. I do have a feature request may be you have thought about it already, I want to be able to pass a keyword to the YARPP and get a lit of related entries, this would come in handy for those who want to display related post outside of wordpress, In my case I was looking to display related posts on my Zenphoto gallery, while they are looking at a picutre.

    Cheers — Great Work.

  3. Epic Alex Says:

    This is also something I've just been looking at for one of my blog readers. Any way we can pass tags manually to yarpp outside WP, and have it pull out related posts from within WP?

  4. mitcho Says:

    Alex, Joe, no, YARPP does not support looking up related posts based on an arbitrary query (such as some keywords or text) due to the way YARPP builds a cache of "relatedness" relations based on pairs of posts, not for strings of text.

    You could, however, write an SQL query similar to that in the YARPP algorithm (found in the depths of magic.php) to do something like this on the fly… the most important component of this would be the "match … against …" MySQL statement, explained here.

  5. sime Says:

    I need to order by dynamically and this post gave me the idea of sending it via query_posts thus making it available to me in the $wp_query global.

    But I will be using parse_query for other things!

    Cheers.

Leave a Reply

mitcho.com uses Markdown with Smarty Pants for simple, lightweight markup.