Howdy! Thanks for coming and enjoy your stay! Take a look at the sitemap and don't forget to chirp!

Customize Posts Order in WordPress via Custom Fields

July 23rd, 2009

Who Said WordPress Isn't Flexible Enough?

Who Said WordPress Isn't Flexible Enough?

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

Related posts:

  1. WordPress: Tag Cloud Based on Author Posts
  2. Custom XML-RPC Methods in WordPress
  3. Custom Post Types in WordPress 3.0
  4. Extending Custom Post Types in WordPress 3.0
  5. The WP-Custom plugin. What’s it all about?

9 Responses to “Customize Posts Order in WordPress via Custom Fields”

  1. rendom

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

    • kovshenin (@kovshenin)

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

  2. привет

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

    пытаюсь выбрать посты со значением 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?

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

    • простите идиота, если отнял время
      забыл объявить $wpdb

      • kovshenin (@kovshenin)

        Именно :) надеюсь проблема решена.

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

    You’re the best!

  4. chasm

    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

    • chasm

      oh, dont talk to colleagues while writing a post comment!
      jquery = wordpress, sorry!

Including pingbacks & trackbacks