Order by meta field forces ignore of null records

[tomauger]

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.

[WraithKenny]

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

[GeertDD]

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

[wonderboymusic]

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

[nacin]

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.

[wonderboymusic]

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

[boonebgorges]

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.

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

[mark8barnes]

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