WordPress.org

Ready to get started?Download WordPress

Forums

[resolved] How to run a WP_Query on both wp_posts and wp_postmeta simultaneously? (14 posts)

  1. Johnny T
    Member
    Posted 5 years ago #

    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 ;-)

  2. Mark / t31os
    Moderator
    Posted 5 years ago #

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

  3. MichaelH
    Member
    Posted 5 years ago #

    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);
  4. Samuel Wood (Otto)
    Tech Ninja
    Posted 5 years ago #

    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.

  5. Mark / t31os
    Moderator
    Posted 5 years ago #

    Cool.... :) ...

  6. Johnny T
    Member
    Posted 5 years ago #

    Brilliant stuff!

    Thanks a million for all your replies.

    They are very very very much appreciated!

    Nice one ;-)

  7. Johnny T
    Member
    Posted 5 years ago #

    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.

    ;-)

  8. dino golman
    Member
    Posted 4 years ago #

    Thanks a lot MichaelH, it's very useful.

  9. seborgarsen
    Member
    Posted 4 years ago #

    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? :-)

  10. seborgarsen
    Member
    Posted 4 years ago #

    $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...

  11. seborgarsen
    Member
    Posted 4 years ago #

    I mean.. the same one :)

  12. alesub
    Member
    Posted 4 years ago #

    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!

  13. alesub
    Member
    Posted 4 years ago #

    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!

  14. Johnny T
    Member
    Posted 4 years ago #

    Great stuff alesub. Thanks for the info ;-)

Topic Closed

This topic has been closed to new replies.

About this Topic