Make WordPress Core

Opened 2 years ago

Last modified 5 months ago

#19653 new enhancement

Order by meta field forces ignore of null records

Reported by: tomauger Owned by:
Milestone: Future Release Priority: normal
Severity: normal Version:
Component: Query Keywords: has-patch needs-unit-tests meta-query
Focuses: Cc:


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.

Attachments (1)

19653.diff (1.0 KB) - added by wonderboymusic 8 months ago.

Download all attachments as: .zip

Change History (9)

comment:1 WraithKenny20 months ago

#18158 is in, but I've no idea how to orderby a meta_value with the query excluding the null records.

comment:2 GeertDD18 months ago

+1 for this enhancement. I really would like to see the option to easily create a LEFT JOIN too.

For now I pulled it off by hooking into the posts_join_paged filter, like this:

add_filter('posts_join_paged', 'use_my_join'), 10, 2);

function use_my_join($join, $wp_query)
        // Note: check some $wp_query->query_vars here first,
        // since the custom join is not needed for all queries.

        global $wpdb;
        $join = "LEFT JOIN $wpdb->postmeta
                ON $wpdb->posts.ID = $wpdb->postmeta.post_id
                AND $wpdb->postmeta.meta_key = 'my_custom_field_name'";

        return $join;

comment:3 johnbillion17 months ago

  • Cc johnbillion added

wonderboymusic8 months ago

comment:4 wonderboymusic8 months ago

  • Keywords has-patch added
  • Milestone changed from Awaiting Review to 3.7

I dislike meta query, but I actually might like this one - I have added a patch that does the following:

  1. Allows a top level key called meta_exclude_empty which defaults to true
  2. Allows a meta_query key called exclude_empty which defaults to true
  3. If meta_exclude_empty evaluates to true at any level, LEFT JOIN will be used instead of INNER JOIN for that level-specific join query

comment:5 WraithKenny8 months ago

  • Cc Ken@… added

comment:6 nacin7 months ago

  • Keywords needs-unit-tests added
  • Milestone changed from 3.7 to Future Release

The patch looks cool, but some unit tests would be really handy. Especially when there are multiple joins going on due to multiple meta queries. It would be good to absolutely make sure it works, obviously.

comment:7 wonderboymusic5 months ago

  • Keywords meta-query added

comment:8 goto105 months ago

  • Cc dromsey@… added
Note: See TracTickets for help on using tickets.