Query & Order posts by custom fields

Question

I'm trying to query a custom post type "jogos" where the meta value "data_de_lancamento" (launch date) is <= than today's. That field uses the format dd/mm/yy.

The returned posts from that query must then be ordered Descending by the custom field "views".

My current code is:

date_default_timezone_set('Europe/Lisbon');
$data = date("d/m/y");
global $wpdb;

$sql = "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 = 'views'
AND wpostmeta2.meta_key = 'data_de_lancamento'
AND wpostmeta2.meta_value < $data
AND wposts.post_type = 'jogos'
AND wposts.post_status = 'publish'
ORDER BY wpostmeta.meta_value ASC";

$resultado = $wpdb->get_results($sql, OBJECT);

Answer 1

You need to use the meta_query parameter of the WP_Class when running a query. Something like this should point you in the right direction.

$data = date("Y-m-d");
$args = array(
    'post_type' => 'jogos',
    'meta_query' => array(
        array(
            'key' => 'data_de_lancamento',
            'value' => $data,
            'compare' => '<='
        )
    ),
    'meta_key' => 'views',
    'orderby' => 'meta_value',
    'order' => 'DESC'
 );
$query = new WP_Query( $args );

The catch with this is that it's nearly impossible to compare dates in the form of 'dd/mm/yy'. As such, this code will not do the trick. You need to first change your date format to 'yyyy-mm-dd'. This is a much more useful way of performing the date comparison. Also, you may need to do some testing with the operator to make sure it's functioning right.

Note that I specify using meta_query which is new to WP 3.1. meta_key and meta_value are deprecated as of WP 3.1; however, to order by meta_value you actually still have to specify meta_key as the value you want it to order by. Confusing and not well documented.

References:

http://codex.wordpress.org/Function_Reference/WP_Query#Custom_Field_Parameters

http://codex.wordpress.org/Function_Reference/WP_Query#Order_.26_Orderby_Parameters