WordPress.org

Support

Support » How-To and Troubleshooting » [Resolved] query_posts by custom field and order by another field

[Resolved] query_posts by custom field and order by another field

  • Hey Guys

    Just a quickie. I want to query_posts by one custom field (‘Color’) and order by a different custom field (‘Display_Order’). Is this possible?

    eg.

    query_posts(meta_key=Color&orderby=Display_Order&order=ASC)

    Thanks!

Viewing 5 replies - 1 through 5 (of 5 total)
  • Took an example from wpdb and modified it. Didn’t test it too much either.

    <?php
    //List all posts with custom field Color, sorted by the value of custom field Display_Order
    //does not exclude any 'post_type'
    //assumes each post has just one custom field for Color, and one for Display_Order
    $meta_key1 = 'Color';
    $meta_key2 = 'Display_Order';
    
    $postids=$wpdb->get_col($wpdb->prepare("
    SELECT      key1.post_id
    FROM        $wpdb->postmeta key1
    INNER JOIN  $wpdb->postmeta key2
                on key2.post_id = key1.post_id
                and key2.meta_key = %s
    WHERE       key1.meta_key = %s
    ORDER BY    key2.meta_value+(0) ASC",
             $meta_key2,$meta_key1)); 
    
    if ($postids) {
      echo 'List of '. $meta_key1  . ' posts, sorted by ' . $meta_key2 ;
      foreach ($postids as $id) {
        $post=get_post(intval($id));
        setup_postdata($post);?>
        <p><a href="<?php the_permalink() ?>" rel="bookmark" title="Permanent Link to <?php the_title_attribute(); ?>"><?php the_title(); ?></a></p>
        <?php
      }
    }
    ?>

    Great work. Will give it a whirl. Thanks!

    I have two custom fields that I want to filer against and sort by. I’ve got this to work for the ordering but what I would like to add is a filter so that only records with an end date after today are shown. Dates are stored in the format m/d/Y. I tried a couple of ways but can’t seem to get it.

    Any help would be much appreciated.

    Probably need to use MySQL’s SUBSTR function in your WHERE clause.

    Or get all the posts, then filter it in your loop.

    I was originally using query_posts but noticed that it wasn’t ordering by the date (which is a custom field). Resurrected the original post where t31os found the solution but thought that this route looked promising too.

    I was hoping that something like:

    $meta_key1 = 'end_date';
    $meta_key2 = 'start_date';
    
    $postids=$wpdb->get_col($wpdb->prepare("
    SELECT      key1.post_id
    FROM        $wpdb->postmeta key1
    INNER JOIN  $wpdb->postmeta key2
                on key2.post_id = key1.post_id
                and key2.meta_key = %s
    WHERE       key1.meta_key = %s
    	   and key1.meta_key > '".date('Y-m-d')."'
    ORDER BY    key2.meta_value+(0) ASC",
             $meta_key2,$meta_key1));

    would do the trick.

Viewing 5 replies - 1 through 5 (of 5 total)
  • The topic ‘[Resolved] query_posts by custom field and order by another field’ is closed to new replies.
Skip to toolbar