{"id":2719,"date":"2013-03-23T08:15:45","date_gmt":"2013-03-23T08:15:45","guid":{"rendered":"http:\/\/www.simonbattersby.com\/blog\/?p=2719"},"modified":"2013-03-20T17:40:27","modified_gmt":"2013-03-20T17:40:27","slug":"querying-wordpress-serialized-custom-post-data","status":"publish","type":"post","link":"https:\/\/www.simonbattersby.com\/blog\/2013\/03\/querying-wordpress-serialized-custom-post-data\/","title":{"rendered":"Querying WordPress serialized custom post data"},"content":{"rendered":"<p>Had a bit of a head-scratcher on this one this afternoon&#8230;<\/p>\r\n<p>I have a couple of custom post types set up, <code>category<\/code> and <code>product<\/code>, and a number of custom field attached to each of these, managed by Elliot Condon&#8217;s outstanding <a href=\"http:\/\/www.advancedcustomfields.com\/\">Advanced Custom Fields<\/a> plugin. In this case, I have a Relationship field defined on <code>product<\/code>, which allows the user to assign a product to one or more <code>category<\/code>. All very lovely, but I then started on a situation where I wanted to show all products which were related to a given category.<\/p>\r\n<p>The <a href=\"https:\/\/codex.wordpress.org\/Class_Reference\/WP_Query\">WP codex<\/a> is a little light on examples, here, but I eventually triumphed with the following:<\/p>\r\n<pre>$args = array( \r\n   'post_type' \t\t=&gt; 'product', \r\n   'posts_per_page'\t=&gt; -1,\r\n   'meta_query'  \t=&gt; array(\r\n\t\t\t      array(\r\n\t\t\t\t     'key'           =&gt; 'category',\r\n\t\t\t\t     'value'         =&gt; '\"'.$category.'\"',<span class=\"code_comment\">\/\/quotes to make sure category 23 does not match category 123, 230 etc\r\n<\/span>\t\t\t\t     'compare'       =&gt; 'LIKE'\r\n\t\t\t      )\r\n\t\t\t   )\r\n);\r\n<span class=\"code_comment\">\/\/get products\r\n<\/span>$wp_query = new WP_Query( $args );\r\n<\/pre>\r\n<p>To explain this a little, &#8216;category&#8217; is the meta_key assigned to the field on <code>product<\/code>, and <code>$category<\/code> is the ID of the category that I&#8217;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 <code>$category<\/code> = 23:<\/p>\r\n<pre>SELECT wp_posts.* FROM wp_posts INNER JOIN wp_postmeta ON (wp_posts.ID = wp_postmeta.post_id) WHERE 1=1 \r\nAND wp_posts.post_type = 'product' \r\nAND (wp_posts.post_status = 'publish' OR wp_posts.post_status = 'private') \r\nAND ( (wp_postmeta.meta_key = 'category' AND CAST(wp_postmeta.meta_value AS CHAR) LIKE '%\\\"23\\\"%') ) \r\nGROUP BY wp_posts.ID \r\nORDER BY wp_posts.post_date DESC<\/pre>\r\n<p>Once I realised what was going on it then becomes straightforward to use LIKE to find matching records. However, there&#8217;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 <code>$category<\/code> 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 &#8216;in_array&#8217; type comparison here, as the existing approach feels a bit clunky.<\/p>\r\n","protected":false},"excerpt":{"rendered":"<p>Had a bit of a head-scratcher on this one this afternoon&#8230; 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&#8217;s outstanding Advanced Custom Fields plugin. In this case, I have a Relationship field defined on product, [&hellip;]<\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[3],"tags":[10],"class_list":["post-2719","post","type-post","status-publish","format-standard","hentry","category-web-design-and-build","tag-wordpress"],"_links":{"self":[{"href":"https:\/\/www.simonbattersby.com\/blog\/wp-json\/wp\/v2\/posts\/2719","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.simonbattersby.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.simonbattersby.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.simonbattersby.com\/blog\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/www.simonbattersby.com\/blog\/wp-json\/wp\/v2\/comments?post=2719"}],"version-history":[{"count":5,"href":"https:\/\/www.simonbattersby.com\/blog\/wp-json\/wp\/v2\/posts\/2719\/revisions"}],"predecessor-version":[{"id":2722,"href":"https:\/\/www.simonbattersby.com\/blog\/wp-json\/wp\/v2\/posts\/2719\/revisions\/2722"}],"wp:attachment":[{"href":"https:\/\/www.simonbattersby.com\/blog\/wp-json\/wp\/v2\/media?parent=2719"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.simonbattersby.com\/blog\/wp-json\/wp\/v2\/categories?post=2719"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.simonbattersby.com\/blog\/wp-json\/wp\/v2\/tags?post=2719"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}