Query CPT on multiple meta keys and values

  • candell

    (@candell)


    9 years, 3 months ago

    Hi

    I have a custom post type that contains a meta value for closing date and another for location which I would like to query on

    My query for getting posts that have a closing date of after today works as intended

    SELECT *
FROM wpsc_posts 
INNER JOIN wpsc_postmeta
ON ( wpsc_posts.ID = wpsc_postmeta.post_id )
WHERE 1=1 
AND ( ( wpsc_postmeta.meta_key = 'closing_date'
AND wpsc_postmeta.meta_value >= '20170412' ) )
AND wpsc_posts.post_type = 'the_jobs'
AND wpsc_posts.post_status = 'publish'
GROUP BY wpsc_posts.ID
ORDER BY wpsc_postmeta.meta_value ASC

    but how do I add another meta key and value for location such as 

    SELECT *
FROM wpsc_posts 
INNER JOIN wpsc_postmeta
ON ( wpsc_posts.ID = wpsc_postmeta.post_id )
WHERE 1=1 
AND ( ( wpsc_postmeta.meta_key = 'closing_date'
AND wpsc_postmeta.meta_value >= '20170412' ) )
AND wpsc_posts.post_type = 'the_jobs'
AND wpsc_posts.post_status = 'publish'

AND ( ( wpsc_postmeta.meta_key = 'job_location'
AND wpsc_postmeta.meta_value >= 'over-here' ) )

GROUP BY wpsc_posts.ID
ORDER BY wpsc_postmeta.meta_value ASC
Viewing 3 replies - 1 through 3 (of 3 total)
  • Moderator bcworkz

    (@bcworkz)

    9 years, 3 months ago

    Add an alias to your meta join INNER JOIN wpsc_postmeta AS wp1
    Then group your meta WHERE clauses like so:

    AND ( 
  ( wpsc_postmeta.meta_key = 'closing_date' AND wpsc_postmeta.meta_value >= '20170412' ) 
  AND 
  ( wp1.meta_key = 'job_location' AND wp1.meta_value >= 'over-here' )
)

    Why not use WP_Query to do this? Complex meta queries are much more accessible in WP_Query compared to raw SQL. Didn’t your original query come from something like WP_Query, then was modified by removing certain elements? The 1=1 AND could be removed too, it’s only there so code generated syntax will always be correct.

    Thread Starter candell

    (@candell)

    9 years, 3 months ago

    Thanks bcworkz.

    The reason for not using WP_Query was simple, I didn’t know how to build up the query using it (I can do simple queries however), hence using plain old sql.

    If you don’t mind, how would you create the above query using WP_Query?

    Thanks

    Moderator bcworkz

    (@bcworkz)

    9 years, 3 months ago

    The doc page is always helpful, I can never remember all the arguments. A good part of the code can be constructed by copy/pasting different elements, editing to fit your situation. Another advantage is looping through the results is simpler with some of the WP_Query methods, allowing you to use many WP template tags, like the_title(), the_content(), the_permalink(), etc. I’ve shown sample loop code as well.

    $args = array(
    'post_type'   => 'the_jobs',
    'post_status' => 'publish',
    'posts_per_page' => -1,  // get all posts
    'meta_query'  =>  array(
        array(
            'key'     => 'closing_date',
            'value'   => '20170412',
            'compare' => '>=',
        ),
        array(
            'key'     => 'job_location',
            'value'   => 'over-here',
            'compare' => '>=',
        ),
    ),
    'meta_key' => 'closing_date',
    'orderby'  => 'meta_value',
    'order'    => 'ASC',
);
$the_query = new WP_Query( $args );
 
// The Loop
if ( $the_query->have_posts() ) {
    echo '<ul>';
    while ( $the_query->have_posts() ) {
        $the_query->the_post();
        echo '<li>' . get_the_title() . '</li>';
    }
    echo '</ul>';
} else {
    // no posts found
}
/* Restore original Post Data */
wp_reset_postdata();

    WP_Query does have some limitations, but you can still use it as a start, then alter the odd part through various filters that are available. If you have trouble with the query, examine the resulting SQL in $the_query->request. That will usually show you what went wrong, then you just need to decide how to fix it.

Viewing 3 replies - 1 through 3 (of 3 total)

The topic ‘Query CPT on multiple meta keys and values’ is closed to new replies.