Meta Queries: support ordering by meta_value but still including items without that key

[jdgrimes]

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 ​1 ​2 ​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
                $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
                $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
                $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.

[jdgrimes]

Initial patch as a proof of concept for WP_Query

[jdgrimes]

Mock up tests demonstrating the desired result

[jdgrimes]

42907.diff​ is an initial patch. It makes the necessary updates to WP_Meta_Query, with some unit tests. It also updates the order by handing of WP_Query. scratch_69.php​ is some basic tests involving WP_Query that demonstrate what the patch makes possible. (These tests pass with the patch applied, BTW.)

I would really like to see this kind of handling in other query classes too though, not just WP_Query. (In particular, I was actually looking for something like this for WP_User_Query.) Ideally though, it seems like the order by handling should be centralized, or at least the part relating to meta queries. Possibly there are already tickets for that even, I haven't checked yet.

Mostly curious whether this is an enhancement that would be considered, before I proceed further, or whether this is too specialized a use-case to warrant handling for it being built-in.

[boonebgorges]

Hi @jdgrimes - Thanks for the thorough ticket and for the initial proof-of-concept.

Personally, I see a use for this. There have been multiple occasions when I've written EXISTS OR NOT EXISTS meta queries like the one you've described above, and I've run into the same limitation on result ordering. More generally, it seems like a gap in the API that ordering by a meta_value makes it impossible to include items without that meta_key (especially since we support multiple orderby clauses). So, I'd say it'd definitely be considered.

I think the following ticket is related. See my comment: https://core.trac.wordpress.org/ticket/29447#comment:1

[jdgrimes]

Looks like maybe this is a duplicate of #19653?