Customize Posts Order in WordPress via Custom Fields

I came across this last night, might be helpful for some of you ;) Sorting posts in a customized order is pretty simple with the WordPress custom fields mechanism and custom SQL queries. Start off with a basic query:

$querystr = "
	SELECT wposts.*
	FROM $wpdb->posts wposts, $wpdb->postmeta wpostmeta
	WHERE wposts.ID = wpostmeta.post_id
	AND wpostmeta.meta_key = 'order'
	AND wposts.post_type = 'post'
	AND wposts.post_status = 'publish'
	ORDER BY wpostmeta.meta_value ASC
";

That will sort the posts in ascending order using the custom field called order. This is how you actually process the query and get into The Loop:

$pageposts = $wpdb->get_results($querystr, OBJECT);

if ($pageposts)
{
	foreach ($pageposts as $post)
	{
		setup_postdata($post);
		// you can use template tags from now on
	}
}

One more thing. How to use more than one custom field in your query? Well, yes, this is SQL, not WordPress, but anyways. Let’s say you want to select all the posts marked complete (custom field) and sorted by order (another custom field). Here you go:

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

Assuming the complete custom field takes a 1 when the post is complete. I used this method in a simple task manager theme I did for WordPress. Did I overuse the word “custom”? ;)

Published 3 years ago with 23 comments tagged , ,