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
  • That's only applicable for grabbing posts from a certain day, not ordering by day. The only date-related option for the 'orderby' parameter is 'date'.
    – jetlej
    Commented Aug 28, 2014 at 22:10
  • Better create a day custom meta.
    – Stubbies
    Commented Aug 30, 2014 at 21:18
  • Wait, don't create anything yet. I'm trying something.
    – Tomás Cot
    Commented Aug 30, 2014 at 21:35

2 Answers 2

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)

12
  • 1
    Somehow it just started working perfectly. Thank you SO much! What a brilliant solution. The bounty is yours :)
    – jetlej
    Commented Aug 30, 2014 at 22:47
  • 1
    Wait, it shouldn't work magically, it should always work. Can you share the code to check?
    – Tomás Cot
    Commented Aug 30, 2014 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
    Commented Sep 1, 2014 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
    Commented Sep 1, 2014 at 10:46
  • @Roberthue, you could try with $orderby_for_query = 'LEFT( wp_posts.post_date, 10 ) DESC ' . $orderby_for_query;
    – Tomás Cot
    Commented Sep 1, 2014 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 and acknowledge you have read our privacy policy.

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