Sorting and Searching by Metadata on WordPress Post Overview Pages

I’ve been working on a site where we have created a custom post type to register credit card transactions. Initially, I decided to use the transaction number as the title for the custom post, but overtime the client has requested a few changes that resulted in a bit of a challenge.

When you visit the transactions page, you can view a list of all the transactions made. This shows up using a regular Overview page like the one you see when you click on Posts or Pages on the admin menu. As you know this overview pages show some information organized in a tabular fashion. One of the changes the client requested was the possibility to add the name of the person who initiated the transaction, and the date when it was initiated. WordPress by default saves the date of each transaction post, but this changes when you edit the transaction. The client needed a date that did not change, so I decided to record the original date as post metadata. The name and the other information related to the transaction is also recorded as post metadata, which means the name of the person who initiated the transaction is post metadata.

Adding those two pieces of information is quite simple. However, the client required that they be sortable, and searchable, and this is not quite as simple. The first thing I did was refer to the famous Custom Post Type Snippets to make you smile post, by yoast. It says there that to make new columns sortable you only need to add this code:


// Make these columns sortable
function sortable_columns() {
  return array(
    'url'      => 'url',
    'referrer' => 'referrer',
    'host'     => 'host'
  );
}

add_filter( "manage_edit-_sortable_columns", "sortable_columns" );

But this, my friends, just like the cake, is a lie. If you do that, you will end up with your column titles looking like they are sortable, but they are not. WordPress will just sort by date, unless you use a set of allowed sort parameters. So I had a crippled sorting functionality, which sure enough would not make my client happy. I decided to look under the hood, and figure out the problem.

What follows is based on WordPress 3.4.2, although the code should work on any version over 3.1. I specify the version 3.4.2 because I will be referring to line numbers in certain files. If you are reviewing a different wp version, they might be different.

I started by opening edit.php, since it is the file used to display the custom post type overview pages. My intention was to familiarize myself with the process that leads to sorting. By reading this file I discovered a few nice things, but did not really help much. At some point I figured that whatever was causing the sort to fail should be on the query itself, since it is the one that returns the set of results, which should be sorted by the time they get returned because it makes sense to leave the sorting work to mySQL, which is built with this capabilities. With that in mind, I decided to open the query.php file (/wp-includes/query.php).

The WP_Query class has a method to get the posts. And this is the one who, well, gets the posts. You can find a lot of nice info by reading that method. It starts on line 1917. But for now we are only interested on the part that sorts the posts, so lets scroll down to line 2323. That is where the adventure begins.

The Orderby Bit.
On line 2323 of the query.php file, there is an if statement that checks if there is an orderby index in the query variables. In our case, following yoast’s advice, we created column title that were supposed to sort the data, but didn’t. Lets look back a bit to see what the code snipped I showed you before does.

First, here is the code again:


// Make these columns sortable
function sortable_columns() {
  return array(
    'url'      => 'url',
    'referrer' => 'referrer',
    'host'     => 'host'
  );
}

add_filter( "manage_edit-_sortable_columns", "sortable_columns" );

If you look at the previous snippets on yoast’s post, you will see he created a few custom columns on the overview page of his custom post. With this piece of code, he is specifying that he wants the columns which are identified by url, referrer, and host to be sortable. This tells wordpress to render those column title with a link that will set the necessary query variable to sort the data accordingly. So far so good. This works, and the columns are rendered in the correct way. But why does the sorting fail?

When you click on one of the column titles, you are redirected to the same page but with the necessary parameters to make sorting work. One of the is them orderby parameter, which sets the orderby query variable in the WP_Query class. On line 2323 of the query.php file, wordpress is checking if that variable has been set, and since it is, then it executes the code in the if statement.

So far everything is as it should. The column titles are rendered properly, the links point to the right place, and the orderby query variable is set. But if you look at line 2329, there is an allowed_keys array, and none of out keys (url, referrer, host) are in that array. Which means our keys are not allowed! No wonder the sorting fails.

If you look at line 2341 you will see that if they key is not in the allowed keys array, nothing happens. The script skips over, and moves on to things that don’t look suspicious. So, how do we overcome this situation?

Well, as it turns out, we need to add our key to the allowed keys array. If you look at line 2330, you will see that if the meta_key query variable is not empty, then it gets added to the allowed keys array, along with meta_value and meta_value_num. So we need to find a way to add our keys to the meta_key query variable.

When I was testing, I added this line:

$q['meta_key'] = 'url';//I used another value because my keys are different, but I'm using url here to stay consistent with yoast's code

right before the if statement. It did not work. My guess is that it was too late to add it. I was not going to leave that there, since that file is wordpress core file, but I just wanted to test what happened. I was trying with a few modifications right in that place to see which one would work, but after a while, I decided to do a little search, and I found this article:

http://justintadlock.com/archives/2011/06/27/custom-columns-for-custom-post-types

It explains how to do the sorting. This is how the code looks modified for our example:


/* Only run our customization on the 'edit.php' page in the admin. */
add_action( 'load-edit.php', 'my_edit_custom_load' );

function my_edit_custom_load() {
	add_filter( 'request', 'my_sort_custom' );
}

/* Sorts the movies. */
function my_sort_custom( $vars ) {

	/* Check if we're viewing the 'movie' post type. */
	if ( isset( $vars['post_type'] ) && 'custom' == $vars['post_type'] ) {

		/* Check if 'orderby' is set to 'duration'. */
		if ( isset( $vars['orderby'] ) && 'url' == $vars['orderby'] ) {

			/* Merge the query vars with our custom variables. */
			$vars = array_merge(
				$vars,
				array(
					'meta_key' => 'url',
					'orderby' => 'meta_value'
				)
			);
		}
	}

	return $vars;
}

Here we are handling sorting for the url column only, but I’m sure you can figure out how to add it for all other columns. As you can see, we are adding our custom key to the meta_key array, and specifying we want to sort by meta_value. This way, when we get to line 2330 on query.php, our meta_value is added to the allowed_keys
array, and the sort is performed with no problems. But we still need to fix the search.

Searching by Meta Value
Searching is another beast. WordPress, by default, searches only in the title and content of the post. You can see this by going to line 2190 on the query.php file. You will see there that the search is performed only on post_title, and post_content. So, how do we search by meta? Lets do another search. One of the results links to this question on stackoverflow:

http://stackoverflow.com/questions/10774547/wordpress-search-via-metadata/10867990#10867990

Where the answer is almost just what we need. To be honest, I did not remember about the meta_query query variable, although I have used it before, but this refreshed my memory. As we now know, we can change and inject query variables using the request filter. So, lets do that:


/* Only run our customization on the 'edit.php' page in the admin. */
add_action( 'load-edit.php', 'my_edit_custom_load' );

function my_edit_custom_load() {
	add_filter( 'request', 'my_sort_custom' );
}

/* Sorts the movies. */
function my_sort_custom( $vars ) {

	/* Check if we're viewing the 'movie' post type. */
	if ( isset( $vars['post_type'] ) && 'custom' == $vars['post_type'] ) {

		/* Check if 'orderby' is set to 'duration'. */
		if ( isset( $vars['orderby'] ) && 'url' == $vars['orderby'] ) {

			/* Merge the query vars with our custom variables. */
			$vars = array_merge(
				$vars,
				array(
					'meta_key' => 'url',
					'orderby' => 'meta_value'
				)
			);
		}

                if(isset($vars['s'])){
			$vars = array_merge(
				$vars,
				array(
					'meta_query' => array(
						array(
							'key' => 'url',
							'value' => $vars['s'],
							'compare' => 'LIKE'
						)
					)
				)
			);
		}
	}

	return $vars;
}

As you can see, I modified our previous function to include the meta_query query variable, but only if it is a search. That is why we check if $vars['s'] is set. Now lets do a search, and be surprised by the lack of results.

Imagine your post has a title “My Transaction” and no content, and url metadata of “http://somestuff.com/interesting/”. When you do a search for “somestuff.com”, you will get no results. That is because the search query ends up something like this:

GET some_cols FROM posts JOIN post_meta WHERE (post.title LIKE "somestuff.com" OR post.content LIKE "somestuff.com") AND (post_meta = "url" AND post_value LIKE "somestuff.com")

This is just pseudo-code, but it expresses what the real query that wordpress does looks like. Some other things are missing, but the important part to explain why the search fails is there.

As you can see there is an AND between the two sets of parens, and that is what is causing the problem. WordPress needs to find a post that has somestuff.com on the title, or the content, AND on the value of the post meta url. IT will not find it. That is like our first problem, because the title does not have, and it most likely will never have the search term. We need to find a way to change that AND for an OR.

Lets dig into the wordpress code again. I decided to search for meta_query in the query.php file, since it seemed like the place to start after I analyzed the query request. I found that wordpress uses a separate class for query requests, and the important part is on line 2496, where there is a call to the get_sql method of the class that wordpress uses for meta queries. I opened the meta.php file (/wp-includes/meta.php). This file contains the WP_Meta_Query class, which is used for meta queries. Lets find the get_sql method.

On line 703 of meta.php we can find the declaration of the get_sql method. Almost at the end of this function, on line 791 we can see that it is possible to run a filter to modify the returned SQL. This is just what we need. Lets add that filter to our function:


/* Only run our customization on the 'edit.php' page in the admin. */
add_action( 'load-edit.php', 'my_edit_custom_load' );

function my_edit_custom_load() {
	add_filter( 'request', 'my_sort_custom' );
}

/* Sorts the movies. */
function my_sort_custom( $vars ) {

	/* Check if we're viewing the 'movie' post type. */
	if ( isset( $vars['post_type'] ) && 'custom' == $vars['post_type'] ) {

		/* Check if 'orderby' is set to 'duration'. */
		if ( isset( $vars['orderby'] ) && 'url' == $vars['orderby'] ) {

			/* Merge the query vars with our custom variables. */
			$vars = array_merge(
				$vars,
				array(
					'meta_key' => 'url',
					'orderby' => 'meta_value'
				)
			);
		}

                if(isset($vars['s'])){
	                add_filter( 'get_meta_sql', 'modify_and_or' );//Add this filter only if there is a search and we are in custom page
			$vars = array_merge(
				$vars,
				array(
					'meta_query' => array(
						array(
							'key' => 'url',
							'value' => $vars['s'],
							'compare' => 'LIKE'
						)
					)
				)
			);
		}
	}

	return $vars;
}

As you can see, right after checking if we are in the middle of a search, we added the filter. This filter calls the modify_and_or function, which I thought would be a good name since we want to modify an AND, and replace it with an OR. This is the function code:


function modify_and_or($join_where){
	$where = $join_where['where'];
	$new_where = preg_replace('/AND/', "OR", $where, 1);
	$join_where['where'] = $new_where;
	return $join_where;
}

It replaces the first instance of AND that it finds for an OR. I did not use str_replace here because it does not let you specify how many replacements you want to perform, which is a shame.

This is all you need to do, now you can sort and search by metadata.

When dealing with this kind of situations, I like to open core wordpress files and modify them a bit here and there to see what is going on under the hood while worpdress runs. I always take great care not to make this changes permanent, and I’ve found echo, and print_r to be valuable tools in these situations.

This are some of the links to documents that I found useful:
yoast.com/custom-post-type-snippets/

http://codex.wordpress.org/Post_Types

http://codex.wordpress.org/Plugin_API/Filter_Reference/request

http://justintadlock.com/archives/2011/06/27/custom-columns-for-custom-post-types

http://stackoverflow.com/questions/10774547/wordpress-search-via-metadata/10867990#10867990

http://codex.wordpress.org/Class_Reference/WP_Query#Custom_Field_Parameters

About these ads