WordPress.org

Make WordPress Core

Opened 6 years ago

Last modified 23 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 needs-refresh
Focuses: Cc:

Description (last modified 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. 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.

Attachments (1)

19653.diff (1.0 KB) - added by wonderboymusic 4 years ago.

Download all attachments as: .zip

Change History (13)

#1 @WraithKenny
5 years ago

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

#2 @GeertDD
5 years 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;
}

#3 @johnbillion
5 years ago

  • Cc johnbillion added

@wonderboymusic
4 years ago

#4 @wonderboymusic
4 years 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

#5 @WraithKenny
4 years ago

  • Cc Ken@… added

#6 @nacin
4 years 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.

#7 @wonderboymusic
4 years ago

  • Keywords meta-query added

#8 @goto10
4 years ago

  • Cc dromsey@… added

#9 @wonderboymusic
3 years ago

  • Keywords needs-refresh added

this used to work - needs tests for key only queries, order by meta value, top-level vars versus meta_query etc

#10 @boonebgorges
3 years ago

The problem with the LEFT JOIN is that it always puts the non-matching post IDs at the top of the list of results, regardless of the ORDER BY clause. See #29447.

#11 @SergeyBiryukov
3 years ago

  • Description modified (diff)

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 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;
}

#12 @mark8barnes
23 months ago

I just ran into this issue. It would be great if the patch could be tested and added.

Note: See TracTickets for help on using tickets.