[wp-trac] [WordPress Trac] #19653: Order by meta field forces ignore of null records

[WordPress Trac]

#19653: Order by meta field forces ignore of null records
-------------------------------------------------+-------------------------
 Reporter:  tomauger                             |       Owner:
     Type:  enhancement                          |      Status:  new
 Priority:  normal                               |   Milestone:  Future
Component:  Query                                |  Release
 Severity:  normal                               |     Version:
 Keywords:  has-patch needs-unit-tests meta-     |  Resolution:
  query needs-refresh                            |     Focuses:
-------------------------------------------------+-------------------------

Old description:

> When doing a sort on posts with a meta value, the way the SQL is
> currently generated in meta.php creates a condition where records that DO
> NOT have the queried meta value are excluded from the results. This may
> or may not be the desired behaviour, but we don't give developers the
> choice without resorting to custom queries or manual rewrites of large
> swathes of the $clauses array.
>
> The issue: the way WP_Meta_Query->get_sql() creates the join on the meta
> key is by setting an inner join on wp_postmeta and then adding the key
> test to the where clause.
>
> I would suggest writing an outer (left) join on wp_postmeta, with the key
> condition in the join. This would also eliminate any potential future
> ambiguity if, for example, you are sorting on one meta key but filtering
> on another, since the key condition would be within the join clause, not
> the where clause:
>

> {{{
> LEFT JOIN wp_postmeta ON wp_posts.ID = wp_postmeta.post_id AND
> wp_postmeta.meta_key = 'my_custom_field_name'
> }}}
>

> Related to ticket 18158 [http://core.trac.wordpress.org/ticket/18158] is
> the question of how we expose this to the developer in the query API.
>

> {{{
> 'meta_key' => self::get_meta_key( 'my_custom_field_name' ),
> 'orderby' => 'meta_value',
> 'exclude_empty_meta' => false
> }}}
>

> If this gets any traction I would be happy to submit a patch.

New description:

 When doing a sort on posts with a meta value, the way the SQL is currently
 generated in meta.php creates a condition where records that DO NOT have
 the queried meta value are excluded from the results. This may or may not
 be the desired behaviour, but we don't give developers the choice without
 resorting to custom queries or manual rewrites of large swathes of the
 $clauses array.

 The issue: the way WP_Meta_Query->get_sql() creates the join on the meta
 key is by setting an inner join on wp_postmeta and then adding the key
 test to the where clause.

 I would suggest writing an outer (left) join on wp_postmeta, with the key
 condition in the join. This would also eliminate any potential future
 ambiguity if, for example, you are sorting on one meta key but filtering
 on another, since the key condition would be within the join clause, not
 the where clause:


 {{{
 LEFT JOIN wp_postmeta ON wp_posts.ID = wp_postmeta.post_id AND
 wp_postmeta.meta_key = 'my_custom_field_name'
 }}}


 Related to ticket #18158 is the question of how we expose this to the
 developer in the query API.


 {{{
 'meta_key' => self::get_meta_key( 'my_custom_field_name' ),
 'orderby' => 'meta_value',
 'exclude_empty_meta' => false
 }}}


 If this gets any traction I would be happy to submit a patch.

--

Comment (by SergeyBiryukov):

 > When doing a sort on posts with a meta value, the way the SQL is
 currently generated in meta.php creates a condition where records that DO
 NOT have the queried meta value are excluded from the results.

 A workaround (based on a [http://wordpress.stackexchange.com/a/55791/52635
 Stack Exchange answer]) that was helpful for me:

 {{{
 function sort_by_checkbox_value_19653( $query ) {
         if ( is_admin() || ! $query->is_main_query() ) {
                 return;
         }

         $query->set( 'meta_key', 'my_custom_field_name' );
         $query->set( 'orderby', array( 'meta_value' => 'DESC', 'ID' =>
 'DESC' ) );

         add_filter( 'get_meta_sql', 'filter_get_meta_sql_19653' );
 }
 add_action( 'pre_get_posts', 'sort_by_checkbox_value_19653' );

 function filter_get_meta_sql_19653( $clauses ) {
         remove_filter( 'get_meta_sql', 'filter_get_meta_sql_19653' );

         // Change the inner join to a left join,
         // and change the where so it is applied to the join, not the
 results of the query.
         $clauses['join']  = str_replace( 'INNER JOIN', 'LEFT JOIN',
 $clauses['join'] ) . $clauses['where'];
         $clauses['where'] = '';

         return $clauses;
 }
 }}}

--
Ticket URL: <https://core.trac.wordpress.org/ticket/19653#comment:11>
WordPress Trac <https://core.trac.wordpress.org/>
WordPress publishing platform