Order by meta value or date?

4 comments

1. If you can explain it in SQL, you can query for it! There are three places where we want to change the default 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 = 'startDate'
       AND CAST(wp_postmeta.meta_value AS CHAR) < '2011-03-23'
   GROUP BY wp_posts.ID
   ORDER BY wp_postmeta.meta_value DESC
   LIMIT 0, 10
   

   • The join should be a left join
   • The where-clause
   • The order

   The join and the where-clause are added via the _get_meta_sql() function. The output is filtered, so we can hook into it:

   add_filter( 'get_meta_sql', 'wpse12814_get_meta_sql' );
   function wpse12814_get_meta_sql( $meta_sql )
   {
       // Move the `meta_key` comparison in the join so it can handle posts without this meta_key
       $meta_sql['join'] = " LEFT JOIN wp_postmeta ON (wp_posts.ID = wp_postmeta.post_id AND wp_postmeta.meta_key = 'startDate') ";
       $meta_sql['where'] = " AND (wp_postmeta.meta_value IS NULL OR wp_postmeta.meta_value < '" . date('Y-m-d') . "')";
       return $meta_sql;
   }
   

   The order clause is filtered through posts_orderby:

   add_filter( 'posts_orderby', 'wpse12814_posts_orderby' );
   function wpse12814_posts_orderby( $orderby )
   {
       $orderby = 'COALESCE(wp_postmeta.meta_value, wp_posts.post_date) ASC';
       return $orderby;
   }
   

   This gives us the following SQL query:

   SELECT wp_posts.*
   FROM wp_posts
   LEFT JOIN wp_postmeta ON (wp_posts.ID = wp_postmeta.post_id AND wp_postmeta.meta_key = 'startDate')
   WHERE 1=1
       AND wp_posts.post_type = 'post'
       AND (wp_posts.post_status = 'publish')
       AND (wp_postmeta.meta_value IS NULL OR wp_postmeta.meta_value < '2011-03-23')
   GROUP BY wp_posts.ID
   ORDER BY COALESCE(wp_postmeta.meta_value, wp_posts.post_date) ASC
   LIMIT 0, 10
   

   Remember to unhook the filters after you did your query, otherwise you will mess up other queries too. And if possible you should not call query_posts() yourself, but modify the main post query that is done by WordPress while setting up the page.

   Log in to Reply

2. In search of the same problem i came to this page. And was inspired by the answer from @jan Fabry, i added his solution.
   Didnt work, due to filter_naming issues.
   So i will post my updated version here, for other seekers:

       add_filter('posts_join_paged', 'pp_sql_join_meta_publication_revision_date');
       add_filter('posts_where_paged', 'pp_sql_where_meta_publication_revision_date');
       add_filter('posts_orderby', 'pp_sql_orderby_meta_publication_revision_date');
   
       $posts = Timber::get_posts($args);
   
       remove_filter('posts_join_paged', 'pp_sql_join_meta_publication_revision_date');
       remove_filter('posts_where_paged', 'pp_sql_where_meta_publication_revision_date');
       remove_filter('posts_orderby', 'pp_sql_orderby_meta_publication_revision_date');
   

   and here the filters themselves:

   function pp_sql_join_meta_publication_revision_date( $meta_sql ){
       $meta_sql = " LEFT JOIN wp_postmeta ON (wp_posts.ID = wp_postmeta.post_id AND wp_postmeta.meta_key = 'publication_revision_date') ";
       return $meta_sql;
   }
   
   function pp_sql_where_meta_publication_revision_date( $meta_sql )
   {
       $meta_sql .= " AND (wp_postmeta.meta_value IS NULL OR wp_postmeta.meta_value IS NOT NULL OR wp_postmeta.meta_value = '')";
       return $meta_sql;
   }
   
   function pp_sql_orderby_meta_publication_revision_date( $orderby )
   {
       $orderby = 'COALESCE(IF(wp_postmeta.meta_value IS NULL or wp_postmeta.meta_value = "", null, wp_postmeta.meta_value), DATE_FORMAT(wp_posts.post_date, "%Y%m%d")) DESC';
       return $orderby;
   }
   

   Log in to Reply

3. try something along the lines of:

   $postedtime = get_post_meta($post->ID, 'startDate');
   
   if($postedtime != null){
   $orderby = $postedtime;
   
   }else{
   $orderby = 'date';
   }
   

   Log in to Reply

4. A query posts call makes only one query, not two. So no, you can’t have it make two separate queries and then concatenate the results.

   Remember, you’re selecting some set of posts here, then displaying them. That set is selected all at once. If you want to get two separate sets of posts and then merge them, then that’s something you’ll have to do yourself with get_posts or similar.

   Log in to Reply