WordPress.org

Ready to get started?Download WordPress

Forums

[resolved] query_posts by custom field and order by another field (6 posts)

  1. serpico
    Member
    Posted 3 years ago #

    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!

  2. MichaelH
    Member
    Posted 3 years ago #

    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
      }
    }
    ?>
  3. serpico
    Member
    Posted 3 years ago #

    Great work. Will give it a whirl. Thanks!

  4. nathan12343
    Member
    Posted 3 years ago #

    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.

  5. MichaelH
    Member
    Posted 3 years ago #

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

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

  6. nathan12343
    Member
    Posted 3 years ago #

    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.

Topic Closed

This topic has been closed to new replies.

About this Topic