Grouping related postmeta data via SQL query

1 Views

I am using someone else’s WP plugin which stores lots of related data in wp_postmeta using an “artificial key”. So effectively we hijack wp_postmeta to create a nested relational database. So one row will have 

 meta_id = 999  
 post_id = 130  
 meta_key = 'event_id'  
 meta_value = '19'

Then 4 other actual wp_postmeta rows that share that post_id contain the rest of the information for that virtual “row” of data. For example

Read More
 meta_id = 989
 post_id = 130
 meta_key = 'Name'
 meta_value = 'Fred'

And we will have lots of “virtual rows” (hundreds) that all share that event_id of 19 (and of course lots of others with different event_ids of their own.

I have tried many SQL queries, but I am stumped. How can I get a table that has grouped info for a common event_id? It doesn’t have to have all the data, just one piece, such as the ‘Name’ field. I tried a subquery like this: 

SELECT meta_value from wp_postmeta 
WHERE meta_key='name' AND 
post_id IN 
(SELECT post_id FROM wp_postmeta 
WHERE meta_key='event_id' AND meta_value='19');

That probably looks laughably stupid – it also doesn’t work. I also tried JOIN. This seems like something that would come up a lot in WP coding, but I have searched and come up pretty much empty.

Post Views: 1

Related posts

Leave a Reply

3 comments

  1. If I understand your question correctly, this should work:

    function get_event_info($event_id = 0, $info = '' ) {
  global $wpdb;
  return $wpdb->get_col(
    $wpdb->prepare(
      "SELECT meta_value from $wpdb->postmeta WHERE 
      meta_key = '%s' AND post_id IN
      (SELECT DISTINCT post_id FROM $wpdb->postmeta WHERE meta_key = 'event_id' AND meta_value= %s )",
      $info, $event_id
    )
  );
}

    Use this like so:

    $names = get_event_info( 19 , 'Name' );
if ( ! empty($names) ) {
  echo '<ul>';
  foreach ($names as $name) {
    echo '<li>' . $name . '</li>';
  }
  echo '</ul>';
}

    Please be sure the second parameter is exactly the same as the meta_key: in your example meta_key = 'Name' but the SQL query is WHERE meta_key='name', case is different!

  2. You can select your posts by using the meta_query parameter in the WP_Query class. Get all the post ID’s of the posts who have a shared event_id of 19.

    You can use the ID’s later on to get all necessary data.

  3. I stumbled in the same problem and get more or less what I wanted in this way:

    SELECT t.post_id, t.meta_value, u.meta_value
FROM  `wp_postmeta` t INNER JOIN  `wp_postmeta` u
ON t.post_id = u.post_id
WHERE
u.meta_key =  'first_key' AND
t.meta_key =  'second_key'