Sort Posts by Number / Convert longtext to int

  • Hello,
    I want to list my custom Posts by their ranking.
    The Problem is, that the meta_value is saved as a longtext in the database.
    So the list shows up the wrong order:
    1
    10
    2
    3
    4

    Here is my code of getting the data fomr the database:

    add_filter('posts_orderby', 'ranking_order');

function ranking_order($orderby)
{
		global $wpdb,$wp_query;	

		$orderby = " (SELECT distinct $wpdb->postmeta.meta_value
		from $wpdb->postmeta
		where ($wpdb->posts.ID = $wpdb->postmeta.post_id) AND $wpdb->postmeta.meta_key ='_ranking'
		ORDER BY CAST('_ranking' AS DECIMAL)DESC)
		ASC, $wpdb->posts.post_date DESC";
	return $orderby;
}

    I’d be glad for any help.

Viewing 3 replies - 1 through 3 (of 3 total)
  • Moderator bcworkz

    (@bcworkz)

    Try this:
    ORDER BY LENGTH(_ranking), _ranking ASC

    Thread Starter joh3

    (@joh3)

    Thanks for the answer, with this statement I doesn’t get any posts back.

    Do I maybe have to put “meta_value_num” anywhere?

    Moderator bcworkz

    (@bcworkz)

    “meta_value_num” is a WP_Query argument, SQL would not know what to do with it. You have the right idea though, but I don’t know what the SQL equivalent would be. My order by length suggestion is certainly a cheap hack, but it does work in some situations. If you get nothing (and you previously did), there is a syntax error somewhere. You could try putting _ranking in quotes. I don’t know SQL syntax that well, like when you need quotes and when you don’t. Also double check the rest of your query for typos.

Viewing 3 replies - 1 through 3 (of 3 total)
  • The topic ‘Sort Posts by Number / Convert longtext to int’ is closed to new replies.