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

Question

I am trying to to use $wpdb to retrieve a list of posts from the Database where the meta_value in the wp_postmeta table contains the current post_ID in inverted commas. eg. "10"

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";}, however it is also returning all the revisions, not just the most recent post.

Here is the code I am currently using:

   $id = get_the_ID();
   $rows = $wpdb->get_results($wpdb->prepare( 
                  "
                  SELECT * 
                  FROM wp_postmeta
                  WHERE meta_key LIKE %s
                      AND meta_value = %s
                  ",
                  'roles_%_production',
                  '%"' . $id . '"%'
              ));

        // loop through the results
        if( $rows ) {
        ......
        }

Any ideas would be greatly appreciated.

Thanks

Answer 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.

Answer 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).

Answer 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')