WordPress.org

Support

Support » How-To and Troubleshooting » [Resolved] How to run a WP_Query on both wp_posts and wp_postmeta simultaneously?

[Resolved] How to run a WP_Query on both wp_posts and wp_postmeta simultaneously?

  • Hi,

    Does anyone know how I can run a query that interrogates both the wp_posts table and the wp_postmeta table.

    Currently I have:

    $params = "post_status=publish,pending&numberposts=8&author=0";
    $query = new WP_Query;
    $results = $query->query($params);

    What I want to do is something like this:

    $params = "post_status=publish,pending&numberposts=8&author=0 AND WHERE wp_postmeta dataX is equal to Y";
    $query = new WP_Query;
    $results = $query->query($params);

    Anyone any ideas on this?

    Even a “it can’t be done” would be better than me tearing my hair out!

    Many thanks for any help you can give me

    John 😉

Viewing 13 replies - 1 through 13 (of 13 total)
  • The code here is relevant..
    http://codex.wordpress.org/Displaying_Posts_Using_a_Custom_Select_Query

    Namingly this part…

    <?php
    	$querystr = "
    	SELECT wposts.*
    	FROM $wpdb->posts wposts, $wpdb->postmeta wpostmeta
    	WHERE wposts.ID = wpostmeta.post_id
    	AND wpostmeta.meta_key = 'tag'
    	AND wpostmeta.meta_value = 'email'
    	AND wposts.post_status = 'publish'
    	AND wposts.post_type = 'post'
    	AND wposts.post_date < NOW()
    	ORDER BY wposts.post_date DESC
    	";
    	$pageposts = $wpdb->get_results($querystr, OBJECT);
    ?>

    You can use that as a reference to create a joined query, just modify as required..

    Didn’t test this but I believe this would also work:

    $params=array(
    'showposts'=>3,
    'post_type' => 'post',
    'post_status' => 'publish,pending',
    'meta_key'=>'dataX',
    'meta_value'=>'Y'
    );
    $query = new WP_Query;
    $results = $query->query($params);

    Yes, the built in query system can select based on meta values. Just provide it the meta_key and meta_value in the parameters and it should work.

    Cool…. 🙂 …

    Brilliant stuff!

    Thanks a million for all your replies.

    They are very very very much appreciated!

    Nice one 😉

    MichaelH

    I’ve tested your method and it works an absolute treat so that will be what I’m using from now on.

    Thank you once again for your help.

    😉

    dino golman
    Participant

    @beytar

    Thanks a lot MichaelH, it’s very useful.

    seborgarsen
    Member

    @seborgarsen

    What if I wanted to select on multiple metakeys?

    this doesn’t work:

    AND wpostmeta.meta_key = 'keyA'
    AND wpostmeta.meta_value = 'ValueA'
    AND wpostmeta.meta_key = 'KeyB'
    AND wpostmeta.meta_value = 'ValueB'

    Any hints? Oh pretty please w sugar on top? 🙂

    seborgarsen
    Member

    @seborgarsen

    $params=array(
    'showposts'=>3,
    'post_type' => 'post',
    'post_status' => 'publish,pending',
    'meta_key'=>'dataX',
    'meta_value'=>'Y'
    );
    $query = new WP_Query;
    $results = $query->query($params);

    This just spits out 3 instances of Y…

    seborgarsen
    Member

    @seborgarsen

    I mean.. the same one 🙂

    alesub
    Participant

    @alesub

    Hi!

    Yes, I’m with the same trouble as seborgarsen, I need to use two meta keys on a query: One to filtering posts and another to sort them.

    This is my query so far:

    SELECT DISTINCT wposts.*
      FROM wp_posts wposts
      LEFT JOIN wp_postmeta wpostmeta ON wposts.ID = wpostmeta.post_id
      LEFT JOIN wp_term_relationships ON (wposts.ID = wp_term_relationships.object_id)
      LEFT JOIN wp_term_taxonomy ON (wp_term_relationships.term_taxonomy_id = wp_term_taxonomy.term_taxonomy_id)
    WHERE wp_term_taxonomy.taxonomy = 'category'
    AND wp_term_taxonomy.term_id IN(4,18,19,20,21)
    AND wpostmeta.meta_key = 'engine'
    AND wpostmeta.meta_value+0 >= 7.5
    AND wpostmeta.meta_key = 'weight'
    ORDER BY wpostmeta.meta_value+0 desc LIMIT 25

    Could I be asking too much?

    Thanks in advance!

    alesub
    Participant

    @alesub

    Hey,

    I’m back with the solution for the query involving two custom fields, it was just a matter of a simple sql trick:

    SELECT wposts.*
    FROM $wpdb->posts wposts, $wpdb->postmeta wpostmeta, $wpdb->postmeta wpostmeta2
    WHERE wposts.ID = wpostmeta.post_id
    AND wposts.ID = wpostmeta2.post_id
    AND wpostmeta.meta_key = 'order'
    AND wpostmeta2.meta_key = 'complete'
    AND wpostmeta2.meta_value = '1'
    AND wposts.post_type = 'post'
    AND wposts.post_status = 'publish'
    ORDER BY wpostmeta.meta_value ASC

    I found it here:
    http://kovshenin.com/archives/customize-posts-order-in-wordpress-via-custom-fields/

    🙂

    Enjoy!

    Great stuff alesub. Thanks for the info 😉

Viewing 13 replies - 1 through 13 (of 13 total)
  • The topic ‘[Resolved] How to run a WP_Query on both wp_posts and wp_postmeta simultaneously?’ is closed to new replies.