WP archives order by custom meta key

3 comments

1.    function my_change_order( $query ) {
           // Check if the query is for an archive
           if($query->is_archive())
           // Query was for archive, then set order
           $query->set( 'order' , 'asc' );
           $query->set( 'meta_query', array(
               array(
                 'key' => '_my_meta_vip'
               )
           ));
   
           // Return the query (else there's no more query, oops!)
           return $query;
       }
   

   look at the following topic: https://wordpress.stackexchange.com/questions/20237/using-meta-query-how-can-i-filter-by-a-custom-field-and-order-by-another-one may give you a clear idea

2. Finally can’t find any way to list all post with and without _my_meta_VIP defined.

   In my solution all _my_meta_VIP was filled with enabled or disabled and the following code do the job:

       // Function accepting current query
       function my_change_order( $query ) {
           // Check if the query is for an archive
           if($query->is_archive()) {
               // Query was for archive, then set order
               $query->set( 'post_type', 'profile' );
               $query->set( 'orderby' , 'meta_value' );
               $query->set( 'meta_key' , '_my_meta_vip' );
               $query->set( 'order', 'DESC' );
           } else {
               // Return the original query for non-archive pages
               return $query;
           }
           // Return the query
           return $query;
       }
       // Runs before the posts are fetched
       add_filter( 'pre_get_posts' , 'my_change_order' );
   

3. It looks like the underlying issue here is that the query is doing an inner join on the posts and postmeta tables, which is why posts that don’t have that particular post meta entry aren’t being returned by the query. What you want is a left join. See this answer for an explanation of the difference.

   You should be able to use the posts_join filter to replace the inner join with a left join:

   add_filter('posts_join', function($join) {
       global $wpdb;
   
       // Replace inner join with left join
       $search = 'INNER JOIN ' . $wpdb->postmeta;
       $replace = 'LEFT JOIN ' . $wpdb->postmeta;
       $join = str_ireplace($search, $replace, $join);
   
       return $join;
   });
   

Comments are closed.