Ordering posts by day (not time) AND meta value

Question

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?

Answer 1

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)

Answer 2

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.