Konstantin Kovshenin

Customize Posts Order in WordPress via Custom Fields

July 23, 2009

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”? ;)

FacebookTwitter

About the author

Konstantin Kovshenin

WordPress Core Contributor, ex-Automattician, public speaker and consultant, enjoying life in Moscow. I blog about tech, WordPress and DevOps.

View all posts

23 comments

  • Привет, а где этот код должен быть? Внутри лупа?
    я использую вот такой способ, и как видишь не могу сделать сортировку по другому филду.
    $recent = new WP_Query("meta_key=custom&orderby=meta_value&cat=$categ&order=ASC&showposts=8");

    • Нет, он должен быть ДО the_loop .. Через WP_Query вряд ли получится поработать с одним, и уж тем более несколькими custom полями, поэтому нужен custom SQL запрос.. В общем вот это вот $recent = new … ни к чему. Попробуй как в моём примере.

  • привет

    у меня какой-то непонятный затык с подобной выборкой. не подскажешь?

    пытаюсь выбрать посты со значением Y в поле _author_column и отсортировать их по значению поля _number_to_show

    $querystr = "
    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 = '_number_2show'
    AND wpostmeta2.meta_key = '_author_column'
    AND wpostmeta2.meta_value = 'Y'
    AND wposts.post_type = 'post'
    AND wposts.post_status = 'publish'
    ORDER BY wpostmeta.meta_value ASC";
    $pageposts = $wpdb->get_results($querystr, OBJECT);
    var_dump($pageposts);

    Получаю непонятно что. Вернее ничего не получаю во-об-ще. Посты на месте, поля заполнены. Почему может не срабатывать выборка по custom fields?

    Спасибо за ответ. Заранее

  • Thank you so much!!! The query with the two custom fields was exactly what I was looking for.

    You're the best!

  • Hi Konstantin,

    nice post.
    I came across the same situation with wordpress lt 3.0.
    There should be a main page with only vip posts on it. From place 1 to 10.
    So i added a meta box called "position" in the backend and had to order the posts with that special meta data.

    First try was to debug the wordpress WP_Query and find out the statement, jquery used. Then i adjusted this statement similar like you did here.

    But i did not feel comfortable, what if the database structure will change on updates. Or maybe some plugin will be missed by the manual statement… bla

    But the wordpress api has a great hook for this:
    meta_key, meta_compare, meta_value

    So i could solved the requirement like this:
    query_posts('meta_key=position&meta_compare=>=&meta_value=1&orderby=meta_value&order=ASC');

    greetz,
    chasm

  • Hi Konstantin,
    just found this post, and I think that I've to do some type of query like this one, but I can't find out how!!

    maybe you can help me with this, Im trying to do a simple thing in wordpress that is just load the posts inside the single.php page. But I have a problem first the code is duplicating the posts and I cant find why, second I just want to show the post next to the date of the page/post that Im in. you can see the idea here: http://tinyurl.com/64sj66r

    any idea on how can I query to only call the posts with the date (time) after the post you're actually in!?

    something like this:

    if this post was done day 14th January at 17.00h show all post after 14th january at 17.00h and not including the actual.

    the code in single.php is like this right now:

    probably you don't have time for this, but Im trying anyway!
    thanks in advance,
    have a great day!

    • You're right, and WordPress 3.1 will also introduce meta_query which will allow multiple uses of meta_key and meta_value. Hopefully it will solve the problem I introduced in this post, but before that you'll have to "hack" the database to do multiple sorting with post meta ;) Cheers!

  • Interesting. I could use this very much. But for some reason I can't get the loop working. Only the first post shows – idea why?

  • Thanks a bunch! – This worked perfectly for me.

    I have a custom post type of 'podcast', with an 'episodeNumber' custom field and I need to sort by episode number in descending order – I got this working with minimal changes to you code.

    The only thing I don't know how to do is add pagination :)

Konstantin Kovshenin