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: wordpress
You are a bloody life saver!!! Thank you soo much for this!!
YOU save my life!
Thanks! =)
Thank you so much, you way is the most easy, better than all recomendations on stackoverflow
U save my day.
Thanks !