CODEWITHSUNDEEP
phpmysqlwordpress
David Matos
David Matos

Reputation: 560

How to order by meta key value in WordPress MySQL query?

I'm trying to query WordPress posts and order them by a custom meta key. Here's the query I have:

SELECT distinct wposts.*
FROM $wpdb->posts wposts, $wpdb->postmeta wpostmeta
WHERE wposts.ID = wpostmeta.post_id
AND wpostmeta.meta_key = 'closed'
AND wpostmeta.meta_value = '1'
AND wposts.post_status = 'publish'
AND wposts.post_type = 'auction'
ORDER BY (wpostmeta.meta_value AND wpostmeta.meta_key = 'end_date') ASC
LIMIT 10

This gets me the posts I want, but it doesn't order them by the meta key 'end_date'. I also tried the following format:

$args = array(
    'post_type' => 'auction',
    'post_status' => 'publish',
    'posts_per_page' => 10,
    'meta_key' => 'end_date',
    'orderby' => 'meta_value',
    'order' => 'DESC',
    'meta_query' => array(
        array(
            'key' => 'closed',
            'value' => '1',
            'compare' => '=',
        )
    )
);

$closed_auctions = new WP_Query( $args );

However, this returns posts in a random order, way more than 10 and duplicated many times.

Also, the field I'm trying to order is a date, in the format MM/DD/YYYY, but I believe it's stored as a longtext. Do I have to do any conversion before ordering it?

Upvotes: 4

Views: 3142

Answers (2)

Nathan Dawson
Nathan Dawson

Reputation: 19338

You can use STR_TO_DATE to convert DD/MM/YYYY into a MySQL date that can be ordered. Ideally you'd change the way dates are stored in the database but if that isn't an option use:

ORDER BY ( STR_TO_DATE( wpostmeta.meta_value, '%d/%m/%Y' ) AND wpostmeta.meta_key = 'end_date' ) ASC

Further reading: http://dev.mysql.com/doc/refman/5.6/en/date-and-time-functions.html#function_str-to-date

Upvotes: 2

user2218824
user2218824

Reputation:

I'm not sure if you can actually sort a value in the format MM/DD/YYYY. It looks like you want to store some kind of a date as a meta value - why aren't you using a timestamp? When using timestamps you can easily sort by using "meta_value_num" as a value for "orderby" (see WP Query Documentation).

Upvotes: 1

Related Questions