I believe this is a bug with the SQL generated in wordpress with this kind of advanced query. Hopefully I’m wrong and someone has a fix for this.
[TL:DR] This advanced query works as expected when I remove the orderby section, and doesn’t work (it returns all posts) otherwise. I have included SQL outputs from the $query->request that show incorrect nesting of the query (unless I’m missing something!).
Background:
Wordpress 4.0.1, running through local Vagrant box (using Chassis for wp dev).
I’m writing an API accessible endpoint to search a custom post type “services” based on some criteria passed in from the front end. The end goal is this will allow an ajax search in two different contexts – one for public, and one for an internal system which just shows some extra services which are only given out by an operator.
Part of this system is a basic geolocation of the services, some are local and others are national. National services should show in results regardless of the ‘postcode’ variable from the form, local ones should only show if the postcode is one they cater to. I’m doing this by populating a hidden custom field called ‘hidden_keyword_search‘ at save time of each service, with lots of bits from the other fields like the title, suburb, post code, and a list of post codes based on a radius from the service’s base post code. Keeps searches quick, and they don’t need to be dynamically geolocated on every search, so it becomes a pretty basic keyword search for the postcode then.
The problem comes when I want to also order these results by another custom meta field, ‘display_ranking‘. This is more or less used to put the trusted reliable services towards the top of the results, it’s just a numeric order (ASC order).
I have two services to test with. One is national, the other is local and only services one post code (not 4000). The query I’m posting to the endpoint is from the internal context (so there is no ‘public’ only clause being added).
This is the content of $service_search_params being passed in:
$service_search_params['numposts'] = 10;
$service_search_params['context'] = 'ccv';
$service_search_params['postcode'] = '4000';
$service_search_params['category'] = null;
$service_search_params['keyword'] = null;
Here’s the PHP used to build the wp_query.
$args = array(
'posts_per_page' => $service_search_params['numposts'],
'post_type' => 'service',
'post_status' => 'publish',
/*
* If I comment out the below 3 lines regarding ordering by the custom field 'display ranking',
* then the postcode search works - but then I have to return all results, and manually
* order & slice the array afterwards. If I leave them in, it returns ALL services.
*/
'meta_key' => 'display_ranking',
'orderby' => 'meta_value_num',
'order' => 'ASC'
);
if ($service_search_params['category']) {
$args['tax_query'] = array(
array(
'taxonomy' => 'service_categories',
'field' => 'id',
'terms' => array(intval($service_search_params['category'])),
),
);
}
if ($service_search_params['keyword']) {
$args['meta_query']['relation'] = 'AND';
$args['meta_query'][] = array(
'key' => 'hidden_keyword_search',
'value' => $service_search_params['keyword'],
'compare' => 'LIKE'
);
}
//public restrictions
if ($service_search_params['context'] != 'ccv') {
$args['meta_query']['relation'] = 'AND';
//only show public results for any search outside the ccv context.
$args['meta_query'][] = array(
'key' => 'public',
'value' => true,
'compare' => '='
);
}
//MUST BE LAST.
if ($service_search_params['postcode']) {
//construct a sub meta query. either the postcode is in
//the search keywords, or the service is national or state.
$pcode_meta_query = array();
$pcode_meta_query[] = array(
'key' => 'hidden_keyword_search',
'value' => $service_search_params['postcode'],
'compare' => 'LIKE'
);
// or the service is state/national.
$pcode_meta_query[] = array(
'key' => 'service_area',
'value' => 'national',
'compare' => '='
);
$pcode_meta_query[] = array(
'key' => 'service_area',
'value' => 'state',
'compare' => '='
);
$pcode_meta_query['relation'] = 'OR';
if (isset($args['meta_query']['relation'])) {
$args['meta_query']['relation'] = 'AND'; //probably redundant, just make sure
$args['meta_query'][] = $pcode_meta_query;
} else {
//no other search vars, so make this the top one.
$args['meta_query'] = $pcode_meta_query;
}
}
$the_query = new WP_Query( $args );
Based on that, when I pass in a ‘postcode’ of 4000 it should only return one service – the national one. Instead it returns both results even though the other service is ‘local’ only and doesn’t have ‘4000’ anywhere. When I comment out the orderby variables and change nothing else, the SQL changes and it correctly returns only the national service.
This is the SQL generated by $the_query->request if I leave the orderby variables in:
// ------ ORDERBY INCLUDED, ONLY 'postcode' (4000) SUPPLIED. RETURNS ALL SERVICES.
SELECT SQL_CALC_FOUND_ROWS sz_posts.ID
FROM sz_posts INNER JOIN sz_postmeta ON sz_posts.ID = sz_postmeta.post_id
INNER JOIN sz_postmeta AS mt1 ON (sz_posts.ID = mt1.post_id)
INNER JOIN sz_postmeta AS mt2 ON (sz_posts.ID = mt2.post_id)
INNER JOIN sz_postmeta AS mt3 ON (sz_posts.ID = mt3.post_id)
WHERE 1=1
AND sz_posts.post_type = 'service'
AND ( (sz_posts.post_status = 'publish') )
AND (
sz_postmeta.meta_key = 'display_ranking'
OR (mt1.meta_key = 'hidden_keyword_search'
AND CAST(mt1.meta_value AS CHAR) LIKE '%4000%')
OR (mt2.meta_key = 'service_area'
AND CAST(mt2.meta_value AS CHAR) = 'national')
OR (mt3.meta_key = 'service_area'
AND CAST(mt3.meta_value AS CHAR) = 'state')
)
GROUP BY sz_posts.ID ORDER BY sz_postmeta.meta_value+0 ASC LIMIT 0, 10
This is the SQL generated by $the_query->request if I comment the orderby variables out:
// ------ ORDERBY REMOVED, ONLY 'postcode' (4000) SUPPLIED. RETURNS CORRECT SUBSET OF SERVICES.
SELECT SQL_CALC_FOUND_ROWS sz_posts.ID
FROM sz_posts INNER JOIN sz_postmeta ON (sz_posts.ID = sz_postmeta.post_id)
INNER JOIN sz_postmeta AS mt1 ON (sz_posts.ID = mt1.post_id)
INNER JOIN sz_postmeta AS mt2 ON (sz_posts.ID = mt2.post_id)
WHERE 1=1
AND sz_posts.post_type = 'service'
AND ((sz_posts.post_status = 'publish'))
AND (
(sz_postmeta.meta_key = 'hidden_keyword_search' AND CAST(sz_postmeta.meta_value AS CHAR) LIKE '%4000%')
OR (mt1.meta_key = 'service_area' AND CAST(mt1.meta_value AS CHAR) = 'national')
OR (mt2.meta_key = 'service_area' AND CAST(mt2.meta_value AS CHAR) = 'state')
)
GROUP BY sz_posts.ID ORDER BY sz_posts.post_date DESC LIMIT 0, 10
Workaround:
Currently I’m getting around this by querying for all results (so post_per_page = -1), then manually sorting by the display_rank and returning an array_slice of the number of results required…
