5

I am trying to fetch a post based on the following meta keys.

  • post_code with 432C
  • location with XYZ Both belong to a CPT. I'm trying to fetch the Post with both these meta_values.

I Don't Want an OR relation, I want a AND relation, I have tried several WP_Query objects and still haven't found a solution after hours of looking.

  • 1
    What actual WP_Query code have you tried? – RRikesh Sep 2 '16 at 6:26
  • $args = array( 'post_type' => 'courses', 'meta_query' => array( array( 'key' => 'post_code', 'value' => '432C', ), array( 'key' => 'location', 'value' => 'XYZ', ), ), ); $query = new WP_Query( $args ); – Sheraz Ahmed Sep 2 '16 at 6:34
10

This should do it. The default relation is AND so that won't need to be specified.

$args = array(
    'post_type'  => 'wpse_cpt',
    'meta_query' => array(
        array(
            'key'     => 'post_code',
            'value'   => '432C',
        ),
        array(
            'key'     => 'location',
            'value'   => 'XYZ',
        ),
    ),
);
$query = new WP_Query( $args );
  • I've tried this, but this just gets me too much information, I'm trying to get results using $wpdb .. Is it possible? – Sheraz Ahmed Sep 2 '16 at 6:36
  • This will return the results that you described in your question. So, either you need to make a more specific query, or make sure that your posts have accurate meta data. – Jeremy Ross Sep 2 '16 at 13:42
  • 1
    And, yes, you can use $wpdb, but you'll get the same results if you use the same criteria. WP_Query is easier and safer. – Jeremy Ross Sep 2 '16 at 13:43
2

Edit Again: Ah, I didn't answer the question. You would select a.* to get all columns of the posts with the matching key values, instead of selecting c.meta_value.

The below sql/$wpdb query will select all meta_value%s of a post with post_type of $postType and a meta_key value of $metaKey. Therefore selecting all the different values of a given meta_key (via '$metaKey'). The term_relationships table is wordpress' helper table for relationship connections in tables. wp_posts is the wordpress 'posts' table, and, wp_postmeta is the wordpress 'postmeta' table. (Note: If you are using custom tables these table names would differ.)

~ Edited to add 'doing' notes, requested by @MikeNGarrett

/* $wpdb is a global var provided by the wordpress 'engine'
** for query'ing wordpress database tables. 
*/
global $wpdb; 
$postType = 'mycustomposttype';
$metaKey = 'mymetakey';
$query = "
    SELECT c.meta_value 
    FROM wp_posts a 
    LEFT JOIN wp_term_relationships b 
        ON (a.ID = b.object_id) 
    LEFT JOIN wp_postmeta c 
        ON (a.ID = c.post_id) 
    WHERE a.post_type = %s AND c.meta_key = %s";

$metaValues = $wpdb->prepare(query, [$postType, $metaKey]);
$metaValues = $wpdb->get_results($metaValues);

Notes: I am just reusing the $metaValues variable. There is no other reason to write the results of $metaValues prepare() back to the $metaValues variable. You do however have to pass $metaValues to get_resluts().

  • 1
    Hi Eric! Can you provide a bit more context? Especially answering "What is this code doing?" and "Why is the best way to accomplish this?" – MikeNGarrett May 8 at 14:56
  • @MikeNGarrett, added notes. Really, you can return whatever values you want from whichever of those three tables with the query. You only need change up which table you are asking/comparing from. Via, a,b,c and . and the column name. – Eric Shoberg May 8 at 19:47
  • @MikeGarrett, "Why is the best way to accomplish this?". I honestly don't know if this is the 'best' way to accomplish this. It does however accomplish. I would be interested if someone found a better/faster way, too. I believe this to be the least taxing way to do this, to the best of my current abilities and knowledge at this time. – Eric Shoberg May 8 at 20:06
  • Thanks! Looks good – MikeNGarrett May 9 at 15:49
0
$args = array(
    'post_type'  => 'wpse_cpt',
    'meta_query' => array(
        'relation' => 'AND' //**** Use AND or OR as per your required Where Clause
        array(
            'key'     => 'post_code',
            'value'   => '432C',
        ),
        array(
            'key'     => 'location',
            'value'   => 'XYZ',
        ),
    ),
);
$query = new WP_Query( $args );
0

SOLUTION

The Solution Accepted below worked, however, I wanted to know how is it working?

This is how it's working

SELECT * FROM wp_posts p, wp_postmeta m1, wp_postmeta m2
    WHERE p.ID = m1.post_id and p.ID = m2.post_id
    AND m1.meta_key = 'key1' AND m1.meta_value = 'value1'
    AND m2.meta_key = 'key2' AND m2.meta_value =  'value2'
    AND p.post_type = 'cpt' AND p.post_status = 'published'
-1

Tried below object parametes

array(
            'key' => 'post_code',
            'value' =>'432C',
            'compare' => '='
        ),
        array(
            'relation' =>'AND',
             array(

            'key' => 'location',
            'value' => 'XYZ',
             'compare' => '=',

          ),
        )
  • It would be great if you can explain the code as how it solves the issue. – bravokeyl Sep 2 '16 at 6:33
  • have already explain that please tried this parameters using this parameters it will automatic solve the issue – Manthan Dave Sep 2 '16 at 6:34
  • You should explain why that code will work. – RRikesh Sep 2 '16 at 6:37

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.