0

I am trying to do something like this one. Ordering posts by day (not time) AND meta value

I want to list blog posts ordered by date (not time) and then within that day, I want to sort them by custom field my_post_rating. This simply outputs posts ordered by rating.

$args = array(
  'post_type' => 'post',
  'posts_per_page' => '-1',
  'meta_key' => 'my_post_rating',
  'orderby' => 'meta_value_num',
  'ignore_sticky_posts' => 1,
);

$new_query = new WP_Query( $args );

I have also tried suggested solution but it did not worked. Please suggest what is wrong?

Edit // Var dump added

string(344) "SELECT wp_posts.* FROM wp_posts INNER JOIN wp_postmeta ON (wp_posts.ID = wp_postmeta.post_id) WHERE 1=1 AND wp_posts.post_type = 'post' AND (wp_posts.post_status = 'publish' OR wp_posts.post_status = 'private') AND (wp_postmeta.meta_key = 'my_post_rating' ) GROUP BY wp_posts.ID ORDER BY wp_postmeta.meta_value+0 DESC "

2
  • add a var_dump of $new_query->request, when using the code from the other question.
    – Tomás Cot
    Sep 1, 2014 at 12:38
  • Added var dump.
    – Robert hue
    Sep 1, 2014 at 13:19

1 Answer 1

2
  add_filter('posts_orderby', 'posts_orderby');

  function posts_orderby($orderby_for_query) {
        global $wpdb;
        $prefix = $wpdb->prefix;
        $orderby_for_query = "LEFT(" . $prefix . "posts.post_date, 10) DESC, " . $orderby_for_query;
        return $orderby_for_query;
    }   

This produces this query:

SELECT   wp_posts.* FROM wp_posts  INNER JOIN wp_postmeta ON (wp_posts.ID = wp_postmeta.post_id) WHERE 1=1  AND wp_posts.post_type = 'post' AND (wp_posts.post_status = 'publish') AND (wp_postmeta.meta_key = 'my_post_rating' ) GROUP BY wp_posts.ID ORDER BY LEFT(wp_posts.post_date, 10) DESC, wp_postmeta.meta_value+0 DESC
7
  • Hey @Tomascot how can we make it weekly instead of daily.
    – Robert hue
    Sep 5, 2014 at 6:19
  • 1
    "YEAR(" . $prefix . "posts.post_date) DESC, WEEK(" . $prefix . "posts.post_date) DESC," . $orderby_for_query; I didn't test it but it should be something like that.
    – Tomás Cot
    Sep 5, 2014 at 11:48
  • it's showing same results. here is the vardump "SELECT SQL_CALC_FOUND_ROWS wp_posts.ID FROM wp_posts INNER JOIN wp_postmeta ON (wp_posts.ID = wp_postmeta.post_id) WHERE 1=1 AND wp_posts.post_type = 'post' AND ((wp_posts.post_status = 'publish')) AND (wp_postmeta.meta_key = 'my_post_rating' ) GROUP BY wp_posts.ID ORDER BY YEAR(wp_posts.post_date) DESC, WEEK(wp_posts.post_date) DESC, wp_postmeta.meta_value+0 DESC"
    – Robert hue
    Sep 5, 2014 at 12:02
  • 1
    It could be showing the same results, have you checked if the posts order ir right?
    – Tomás Cot
    Sep 5, 2014 at 12:09
  • 1
    The key is to know that MySQL has a lot of date functions builtin, so you just have to hit the docs, and the naming is great, so it's easy to know what you are looking for.
    – Tomás Cot
    Sep 5, 2014 at 12:21

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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