WordPress.org

Ready to get started?Download WordPress

Forums

[resolved] How do i sort posts by a custom field? (14 posts)

  1. oskarlin
    Member
    Posted 5 years ago #

    OK, part of my website (centrumok.se) has posts showing events coming up soon. They are sorted so that the event that coming up in nearest time is shown first at the top etc.

    Right now these posts are ordered by publishing date which means I have to change the publishing date for a post if I am putting in a new event within all the others.

    So I decided to have custom fields for the date of the event so I could order the posts by the date taken from the custom field, but it doesn't work with plain ol' get_posts since there is no orderby a certain custom field.

    Does anyone have an idea of how to make this work? How do I make this query so that it gets ordered by the custom field?

  2. oskarlin
    Member
    Posted 5 years ago #

    I just did an inner join with the meta_data table and hey it works perfect!

  3. rosschapman
    Member
    Posted 5 years ago #

    Hi oskarlin,

    A client of mind just requested this feature. Great timing! Do you mind posting your solution so the community can benefit? Thanks!!

  4. Dgold
    Member
    Posted 5 years ago #

    This question has been asked a few times. I would really appreciate if someone (or more than 1 person) could explain this in the Codex!

    For me, I would like to:
    Order posts by a Custom field (alphabetical order of the custom field), and then sort those results by alphabetical order of the Post Title.

    I know that sounds weird for most blogs, but in my case, each Post is a Poem. So the title is the Poem Title. Each post has a custom field showing the poet's name. I want to display a list of poems by each poet. It would be an alphabetical list showing first the Poet's name, then every poem by that poet in alphabetical order.

    The output I want:

    A Poet -- "Poem A"
    A Poet -- "Poem B"
    A Poet -- "Poem C"
    B Poet -- "Apple"
    B Poet -- "Bear"
    C Poet -- "Airplane"

    etc.

  5. Gabriel Radic
    Member
    Posted 5 years ago #

    I have exactly the Event date problem myself and I'm curious how the issues was "resolved". Says [resolved] so in the title.

  6. mikey1
    Member
    Posted 5 years ago #

  7. James M
    Member
    Posted 5 years ago #

    What was the answer to sort by a custom field?

    I currently want to sort by a date that is not the publish date.

    I would like to set a date as a custom field and use that to order the posts shown from The Loop.

    I don't care to display that date, just sort The Loop by it.

  8. MichaelH
    Member
    Posted 5 years ago #

    You'll want to change the ORDERBY clause but Displaying_Posts_Using_a_Custom_Select_Query should get you headed in the right direction.

  9. p4blo32
    Member
    Posted 5 years ago #

    Hi guys, just thought I'd add I needed to do this (sort posts by event date rather than post date) for a project I'm working on, and this is my solution:

    $querystr = "
    			SELECT wposts.*
    			FROM $wpdb->posts wposts, $wpdb->postmeta wpostmeta
    			WHERE wposts.ID = wpostmeta.post_id
    			AND wpostmeta.meta_key = 'date'
    			AND wposts.post_status = 'publish'
    			AND wposts.post_type = 'post'
    			ORDER BY STR_TO_DATE(wpostmeta.meta_value, '%m/%d/%Y') ASC
    			";
    
    		$pageposts = $wpdb->get_results($querystr, OBJECT);

    The date there must be inputted in the American format ie 21/02/2009 for it to work, hopefully my client will be ok with that!

    The reason for using that date format is for later handling it in PHP, of course you could store the date in YYYY/MM/DD format and thus the STR_TO_DATE clause would be redundant.

  10. mattifesto
    Member
    Posted 5 years ago #

    Hmmm... I'm surprised nobody mentioned there's a very easy way to do this.

    Assuming you have a custom field you've named 'popularity' all you have to do is place this before your first call to have_posts() in archive.php (for instance if you wanted all category views etc to be sorted by popularity)

    query_posts('&meta_key=popularity&orderby=meta_value');

    It's as simple as that.

    Caveats: This will exclude posts that don't have a popularity custom field, but for people that know every post will have some value for a custom field, it's a much easier and less fragile method of sorting by a custom field.

    (btw, I was unable to get an ampersand to show up in a code block on these comments. It would always show as & no matter what, so I just used blockquote instead.)

  11. MichaelH
    Member
    Posted 5 years ago #

    The meta_key stuff is newer to query_posts and likely wasn't available when this topic originated.

  12. mattifesto
    Member
    Posted 5 years ago #

    Just a note where I made a mistake. I was trying to do this on the archive page, and you have to remember to include the existing query_string in the query or that information will be lost which means your category view will no longer be a category view.

    It also means functions like is_category() won't work anymore after you call query_post because I assume they use the query_string.

    So this is the more correct version:

    query_posts($query_string . '&meta_key=popularity&orderby=meta_value');

  13. tsc_a
    Member
    Posted 5 years ago #

    @mattifesto

    Caveats: This will exclude posts that don't have a popularity custom field, but for people that know every post will have some value for a custom field, it's a much easier and less fragile method of sorting by a custom field.

    I definitely can't live with that, so I looked for another solution.
    Finally I asked a more "pro"-person, who came up with the following:

    add_filter('posts_join', 'new_join' );
    function new_join($pjoin){
    	if(is_category()){
    		global $wpdb;
    		$pjoin .= "LEFT JOIN (
    SELECT *
    FROM $wpdb->postmeta
    WHERE meta_key =  'add_year_value' ) AS metasort
    ON $wpdb->posts.ID = metasort.post_id";
    	}
    	return ($pjoin);
    }
    
     add_filter('posts_orderby', 'new_order' );
    function new_order( $orderby ){
    	global $wpdb;
    	if(is_category()){
    		$orderby = "metasort.meta_value ASC";
    	}
    
     	return $orderby;
    }

    Articles without the "add_year_value" -custom field also show up now.
    Hope it helps someone.

  14. nzde
    Member
    Posted 5 years ago #

    In case it helps, this is the code I used, after a lot of digging:

    <h5>Legal events</h5> <!-- The 7 next legal events -->
    
    					<?php
    
    					$querystr = "
    					SELECT * FROM $wpdb->posts
    					LEFT JOIN $wpdb->postmeta ON($wpdb->posts.ID = $wpdb->postmeta.post_id)
    					LEFT JOIN $wpdb->term_relationships ON($wpdb->posts.ID = $wpdb->term_relationships.object_id)
    					LEFT JOIN $wpdb->term_taxonomy ON($wpdb->term_relationships.term_taxonomy_id = $wpdb->term_taxonomy.term_taxonomy_id)
    					WHERE $wpdb->term_taxonomy.term_id = 13
    					AND $wpdb->term_taxonomy.taxonomy = 'category'
    					AND $wpdb->posts.post_status = 'publish'
    					AND $wpdb->postmeta.meta_key = 'tdo_date'
    					ORDER BY $wpdb->postmeta.meta_value ASC limit 7
    					";
    
    					 $pageposts = $wpdb->get_results($querystr, OBJECT);
    
    					?>
    					 <?php if ($pageposts): ?>
    					  <?php foreach ($pageposts as $post): ?>
    					    <?php setup_postdata($post); ?>
    
    					<div id="lists">
    
    					<ul>
    					<li><a href="<?php the_permalink() ?>" title="<?php the_title(); ?>"><?php the_title(); ?></a></li>
    
    					</ul>
    
    					</div>
    
    					  <?php endforeach; ?>
    
    					 <?php endif; ?>

Topic Closed

This topic has been closed to new replies.

About this Topic