5

I would like to have a list of posts that is first ordered by day, and then within that day ordered user votes (stored as a meta field).

Would it be better to convert each timestamp to the day at the time of the query, or to create another meta field that stores the day? Or is there a better solution?

4
+50

This is what I've found. I'm using a filter to filter the ORDER BY on the SQL query generated by WP_Query. The filter is this posts_orderby. And with it, you can write a custom ORDER BY for the query. I'm gonna show you an example.

add_filter('posts_orderby', 'posts_orderby');

function posts_orderby($orderby_for_query) {
    $orderby_for_query = "LEFT(wp_posts.post_date, 10) DESC, wp_posts.post_title ASC";
    return $orderby_for_query;
}   

The key here is the LEFT function, it's a MYSQL function. With this, you can "modify" the date, so MYSQL uses just the first 10 chars, so it uses the year, month and day, and not the time, which I think is what you wanted.

edit: as pointed by adelval, the DATE function could be used instead of LEFT.

You might need to remove the filter to avoid affecting other queries, you can do it like this:

remove_filter( 'posts_orderby', 'posts_orderby' );

Add that after the call to new WP_Query(). I hope this helped, if you have any doubt, ask.

EDIT: Just as recommendation, it is a good idea to avoid hardcoding the DB prefix, you can get it with this code.

global $wpdb;    
$prefix = $wpdb->prefix;

Then, just prepend that to the table names. For tablename.column

$prefix . 'tablename.column'

By default, the prefix is 'wp_'.

To check the generated SQL query you can var_dump($name_of_query->request)

  • 1
    Somehow it just started working perfectly. Thank you SO much! What a brilliant solution. The bounty is yours :) – jetlej Aug 30 '14 at 22:47
  • 1
    Wait, it shouldn't work magically, it should always work. Can you share the code to check? – Tomás Cot Aug 30 '14 at 22:49
  • I figured out it was because the meta field I was sorting by did not have a default value (of 0) so it would not included many posts in the query. Your code works flawlessly. Thanks! – jetlej Sep 1 '14 at 5:44
  • @TomásCot I have added a code (in answer) and it's not working. Can you check what I am doing wrong. I am sorting blog posts date wise and then within that date, by custom field "my_post_rating" – Robert hue Sep 1 '14 at 10:46
  • @Roberthue, you could try with $orderby_for_query = 'LEFT( wp_posts.post_date, 10 ) DESC ' . $orderby_for_query; – Tomás Cot Sep 1 '14 at 11:37
0

I am using a solution that mixes a meta field and a data as order parameters.

$query = new WP_Query( array(
            'post_type' => 'post',
            'meta_key' => '[your vote field]',
            'orderby' => 'date meta_value_num',
            'order' => 'DESC',
            'meta_query' => array(
                                    array(
                                            'key' => '[your vote field]',
                                            'value' => [what values],
                                            'compare' => '[IN - NOT IN, you decide]',
                                    )
                            ),
            'date_query' => array(
                                    'after' => '1 day ago',
                            )
            ));

Then you also have to add this to your functions.php:

function wpse159469_posts_orderby( $orderby, $query ) {
    return implode( ' DESC,', explode( ',', str_replace( array(' ASC', ' DESC' ), '', $orderby ) ) ) . ' DESC';
}

and remember to add the filter before and after your query:

add_filter( 'posts_orderby', 'wpse159469_posts_orderby', 10, 2 );
//query and stuff
remove_filter( 'posts_orderby', 'wpse159469_posts_orderby', 10, 2 );

This solution works for me.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.