SQL Query for multiple values and multiple keys in the same table

1 Views

Every post has two post meta: year and month (custom)

I need SQL query to pull all posts that have

Read More

wp_postmeta.meta_key = year AND wp_postmeta.meta_value = 2013

AND AT THE SAME TIME another meta_key / meta_value pair

wp_postmeta.meta_key = month AND wp_postmeta.meta_value BETWEEN 1 AND 12 ; 

SELECT * FROM wp_postmeta
WHERE (meta_key = 'agam_post_options_year' AND meta_value = 2013)
    OR (meta_key = 'agam_post_options_month' AND meta_value BETWEEN 0 AND 12 )
GROUP BY meta_value;

This is something I tried and a couple of more variations but it doesn’t do much… this specifically gives me one post that has year 2013 and 12 posts that have either 1 or … to 12 for month field}

Post Views: 1

Related posts

Leave a Reply

3 comments

  1. Please replace _PUT_YOUR_POST_ID_FIELD_HERE_ by something appropriate field from that table and try to run…

    SELECT Y.meta_value, M.meta_value FROM wp_postmeta as Y
JOIN wp_postmeta AS M USING (_PUT_YOUR_POST_ID_FIELD_HERE_) 
WHERE (Y.meta_key = 'agam_post_options_year' AND Y.meta_value = 2013)
AND (M.meta_key = 'agam_post_options_month' AND M.meta_value BETWEEN 0 AND 12 )
GROUP BY Y.meta_value, M.meta_value;

  2. OK It was all good. The reason it took out the “duplicates” was because we didn’t group by Y.meta_id and instead we used meta_value

  3. If you don’t really need a pure SQL query and you’re getting these posts for using them in WordPress, you could use meta_query array passed as a part of args to WP_Query class or get_posts() function.

    $args = array(
 'posts_per_page' => -1,
 'post_type'      => 'your-post-type', // default 'post'
 'meta_query'     => array(
      relation => 'AND', // default 'AND' it might be 'OR'
      array(
          'key'   => 'agam_post_options_year',
          'value' => '2013',
          'compare'=> '=',
          'type'  => 'NUMERIC' // see docs for more types
      ),
      array(
          'key'   => 'agam_post_options_month',
          'value' => array( 0, 12 ),
          'compare' => 'BETWEEN',
          'type'  => 'NUMERIC' // see docs for more types
      )
   )
);

$posts = get_posts( $args ); // returns an array of posts objects
//OR
$query = new WP_Query( $args ); // returns a new WP_Query object

    Hope it helps! : )