Querying WordPress serialized custom post data

Had a bit of a head-scratcher on this one this afternoon…

I have a couple of custom post types set up, category and product, and a number of custom field attached to each of these, managed by Elliot Condon’s outstanding Advanced Custom Fields plugin. In this case, I have a Relationship field defined on product, which allows the user to assign a product to one or more category. All very lovely, but I then started on a situation where I wanted to show all products which were related to a given category.

The WP codex is a little light on examples, here, but I eventually triumphed with the following:

$args = array( 
   'post_type' 		=> 'product', 
   'posts_per_page'	=> -1,
   'meta_query'  	=> array(
			      array(
				     'key'           => 'category',
				     'value'         => '"'.$category.'"',//quotes to make sure category 23 does not match category 123, 230 etc
				     'compare'       => 'LIKE'
			      )
			   )
);
//get products
$wp_query = new WP_Query( $args );

To explain this a little, ‘category’ is the meta_key assigned to the field on product, and $category is the ID of the category that I’m passing in. It took me a little while to understand the SQL generated here. What happens is that meta_query casts the serialized data into characters, like this, where $category = 23:

SELECT wp_posts.* FROM wp_posts INNER JOIN wp_postmeta ON (wp_posts.ID = wp_postmeta.post_id) WHERE 1=1 
AND wp_posts.post_type = 'product' 
AND (wp_posts.post_status = 'publish' OR wp_posts.post_status = 'private') 
AND ( (wp_postmeta.meta_key = 'category' AND CAST(wp_postmeta.meta_value AS CHAR) LIKE '%\"23\"%') ) 
GROUP BY wp_posts.ID 
ORDER BY wp_posts.post_date DESC

Once I realised what was going on it then becomes straightforward to use LIKE to find matching records. However, there’s a potential gotcha in there, which, in my example, would manifest if there was another category with and ID of 123, 230, 2300 etc etc. Without the quotes wrapped around my $category variable here, then the query would return products linked to any of these categories as well. It seems as if meta_query should allow an ‘in_array’ type comparison here, as the existing approach feels a bit clunky.

Tags:

4 responses to “Querying WordPress serialized custom post data”

  1. Alex says:

    U save my day.
    Thanks !

  2. Richzendy says:

    Thank you so much, you way is the most easy, better than all recomendations on stackoverflow

  3. Julio says:

    YOU save my life!
    Thanks! =)

  4. Zync says:

    You are a bloody life saver!!! Thank you soo much for this!!

Useful? Interesting? Leave me a comment

I've yet to find a way of allowing code snippets to be pasted into Wordpress comments - so if you're trying to do this you'd be better off using the contact form.