10

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.

2
  • 1
    What actual WP_Query code have you tried?
    – RRikesh
    Sep 2, 2016 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 ); Sep 2, 2016 at 6:34

6 Answers 6

16

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 );
3
  • I've tried this, but this just gets me too much information, I'm trying to get results using $wpdb .. Is it possible? Sep 2, 2016 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. Sep 2, 2016 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. Sep 2, 2016 at 13:43
3
$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 );
3

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

4
  • 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?" May 8, 2019 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. May 8, 2019 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. May 8, 2019 at 20:06
  • Thanks! Looks good May 9, 2019 at 15:49
1

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'
0

I tried using the WP_Query solution suggested above, but got an empty result. The one suggested by Sheraz Ahmed works fine. Here's my own take for what it's worth:

//database
wp_posts
+---------+--------------------+--------------+
|  ID     | post_title         | post_type    |
+---------+--------------------+--------------+
| 8567    | Intro to MySQL     | talk         |
+---------+--------------------+--------------+
| 8590    | Intro to PHP       | talk         |
+---------+--------------------+--------------+

wp_postmeta
+---------------+------------+----------------+
|  post_id      | meta_key   | meta_value     |
+---------------+------------+----------------+
| 8567          | speaker    | John Doe       |
+---------------+------------+----------------+
| 8567          | year_of    | 2021           |
+---------------+------------+----------------+
| 8590          | speaker    | John Doe       |
+---------------+------------+----------------+
| 8590          | year_of    | 2021           |
+---------------+------------+----------------+


//search by speaker and Year
$speaker = "John Doe";
$year_of = "2021";

$talks = $wpdb->get_results(  
     $wpdb->prepare( 
         'SELECT post_title, meta_value AS speaker 
         FROM '.$wpdb->prefix.'posts AS pt, '.$wpdb->prefix.'postmeta AS pm 
         WHERE post_type = %s AND pm.post_id = pt.id AND ( meta_key = %s AND meta_value = %s ) 
         AND post_id IN ( SELECT post_id FROM nm_postmeta WHERE meta_key = %s AND meta_value = %s )',
         array( 'talk', 
                'speaker', 
                $speaker, 
                'year_of', 
                $year_of )
    )
);
                
foreach ( $talks as $talk ) {
    echo "<p>".$talk->speaker.': '.$talk_homily->post_title."</p>";
}

John Doe: Intro to MySQL

John Doe: Intro to PHP

-1

Tried below object parametes

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

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

          ),
        )
3
  • It would be great if you can explain the code as how it solves the issue.
    – bravokeyl
    Sep 2, 2016 at 6:33
  • have already explain that please tried this parameters using this parameters it will automatic solve the issue Sep 2, 2016 at 6:34
  • You should explain why that code will work.
    – RRikesh
    Sep 2, 2016 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.