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 "

  • add a var_dump of $new_query->request, when using the code from the other question. – Tomás Cot Sep 1 '14 at 12:38
  • Added var dump. – Robert hue Sep 1 '14 at 13:19
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
  • Hey @Tomascot how can we make it weekly instead of daily. – Robert hue Sep 5 '14 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 '14 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 '14 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 '14 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 '14 at 12:21

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.