[wp-trac] [WordPress Trac] #42907: Meta Queries: support ordering by meta_value but still including items without that key

[WordPress Trac]

#42907: Meta Queries: support ordering by meta_value but still including items
without that key
-------------------------+-----------------------------
 Reporter:  jdgrimes     |      Owner:
     Type:  enhancement  |     Status:  new
 Priority:  normal       |  Milestone:  Awaiting Review
Component:  Query        |    Version:  trunk
 Severity:  normal       |   Keywords:
  Focuses:               |
-------------------------+-----------------------------
 Consider this scenario:

 - You want to order a query's results by `meta_value` of a particular
 `meta_key`.
 - Not all items have that key/value pair set.
 - You want to still include those items in the results.
 - You want them to be ordered as if the `meta_value` is `NULL` (or `0` if
 using `meta_value_num`).

 There is currently no way to do this. (But it is a desired feature, see
 [https://wordpress.stackexchange.com/questions/28409/way-to-include-posts-
 both-with-without-certain-meta-key-in-args-for-wp-query 1]
 [https://wordpress.stackexchange.com/questions/102447/sort-on-meta-value-
 but-include-posts-that-dont-have-one?noredirect=1&lq=1 2]
 [https://stackoverflow.com/questions/17016770/wordpress-order-by-meta-
 value-if-it-exists-else-date 3].)

 If you don't care about the items that don't have the meta key set being
 included, then you can use `compare` => `EXISTS`.
 {{{#!php
 <?php
                 $query = new WP_Query(
                         array(
                                 'orderby' => 'meta_value',
                                 'meta_query' => array(
                                         array(
                                                 'key' => 'test',
                                                 'compare' => 'EXISTS',
                                         ),
                                 ),
                         )
                 );
 }}}

 However, that won't work if we want to include results where the key/value
 pair doesn't exist.

 A workaround is to do something like this:

 {{{#!php
 <?php
                 $query = new WP_Query(
                         array(
                                 'orderby' => 'meta_value',
                                 'meta_query' => array(
                                         'relation' => 'OR',
                                         array(
                                                 'key' => 'test',
                                                 'compare' => 'EXISTS',
                                         ),
                                         array(
                                                 'key' => 'test',
                                                 'compare' => 'NOT EXISTS',
                                         ),
                                 ),
                         )
                 );
 }}}


 This approach solves the problem of the items with no key/value pair not
 being included. However, it does not order those items in the results
 reliably. Instead of the `meta_value` being interpreted as `null` (which
 we might expect), the database will choose another `meta_value` entry for
 that item to order that item based on.

 My proposal is to add a `NONE` option for `compare`, so that it would be
 possible to achieve the desired ordering with a query like this:

 {{{#!php
 <?php
                 $query = new WP_Query(
                         array(
                                 'orderby' => 'meta_value',
                                 'meta_query' => array(
                                         'relation' => 'OR',
                                         array(
                                                 'key' => 'test',
                                                 'compare' => 'NONE',
                                         ),
                                 ),
                         )
                 );
 }}}

 This would result in a query that included a `LEFT JOIN` on the meta table
 for that `meta_key`, but did not include any conditions in the query.
 (This is basically a `NOT EXISTS` query but without the `ID = NULL`
 requirement, so that it is just a left join and not a left outer join.)

 When using `meta_value_num` in particular though, ordering by `NULL` may
 not be desirable (those results will be after all other results); `0` may
 be preferred as the imputed value (consider a case when negative values
 are present). To make this possible, I suggest allowing the clause to also
 specify a `default` value, which will be used in the `ORDER BY` clause
 with `COALESCE`.

 Initial patch forthcoming.

--
Ticket URL: <https://core.trac.wordpress.org/ticket/42907>
WordPress Trac <https://core.trac.wordpress.org/>
WordPress publishing platform