Using $wpdb to query posts with meta value containing current post_id

3 comments

1. In code you posted you do not retrieve ‘list of posts’ as you say, but retrieve a list of rows in the meta table. If you want really retrieve a list of posts rely on WP_Query using meta_query param.

   Something like:

   $id = '10'; // unserialized value
   
   $args = array(
     'post_type' => 'post',
     'post_status' => 'publish',
     'posts_per_page' => -1,
     'meta_query' => array(
       array(
         'key' => 'roles_%_production',
         'value' => $id,
         'compare' => 'LIKE'
       )
     )
   );
   $query = new WP_Query( $args );
   $rows = $query->get_posts();
   

   If you want to use $wpdb (I don’t know why) right query is something like:

   <?php
   $id = '10'; // unserialized value
   
   global $wpdb;
   $rows = $wpdb->get_col( $wpdb->prepare(
     "SELECT DISTINCT $wpdb->posts.ID FROM $wpdb->posts, $wpdb->postmeta
     WHERE $wpdb->posts.ID = $wpdb->postmeta.post_id AND
     $wpdb->posts.post_status = 'publish' AND
     $wpdb->posts.post_type = 'post' AND
     $wpdb->postmeta.meta_key = %s AND
     meta_value = %s",
     'roles_%_production',
     $id
   ) );
   ?>
   

   $rows will contain an array of posts IDs. I’ve done this change to make a sense to the use of $wpdb. If you want retrieve all the fields use SELECT * instead of SELECT DISTINCT $wpdb->posts.ID, use $wpdb->get_results instead of $wpdb->get_col and add the line GROUP BY $wpdb->posts.ID to the query.

2. Two notes:

   Searching meta data

   The rule is simple: Serialized meta data (like an array converted to a:1:{i:0;s:2:"10";}) is not meant to be searchable. You’ll have to convert your dataset to single values, so you can do proper meta_query searches.

   The only really working way to search this is to query all the data, then deserialize it, render it if it matches your criteria, or skip it. There’re enough discussions and Q/As on SO that cover that topic.

   Prepared statements

   There’s like_escape(), which has to be used like this:

   "%".like_escape( $string )."%"
   

   The reason why you need to pre- and append the % chars is simple: You can decide your self if the LIKE should happen on both ends or only a single one (start, end, both sides).

3. The inverted commas are the make sure that 10 doesn’t also match 100 etc.

   I can get it work work when I paste the exact of the meta value ie: a:1:{i:0;s:2:”10″;}

   This is because in your WHERE query, you used = where the value contains %. Using LIKE instead would fix this particular issue. Change meta_value = %s to meta_value LIKE %s, and your query should work the same as though you used the full serialised string.

   it is also returning all the revisions, not just the most recent post.

   Make sure you specify the correct post type and status when querying your results. If you want to include all posts that aren’t revisions, then you’ll need to ensure you join the posts table (before your WHERE statement).

   LEFT JOIN $wpdb->posts ON $wpdb->posts.ID = $wpdb->postmeta.post_id
   

   and include the following line in your WHERE statement (linked by an AND).

   $wpdb->posts.post_type NOT IN ('revision')
   

Comments are closed.