I just did an inner join with the meta_data table and hey it works perfect!
Hi oskarlin,
A client of mind just requested this feature. Great timing! Do you mind posting your solution so the community can benefit? Thanks!!
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.
I have exactly the Event date problem myself and I’m curious how the issues was “resolved”. Says [resolved] so in the title.
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.
You’ll want to change the ORDERBY clause but Displaying_Posts_Using_a_Custom_Select_Query should get you headed in the right direction.
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.
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.)
The meta_key stuff is newer to query_posts and likely wasn’t available when this topic originated.
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’);
@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.
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; ?>
