Multiple meta filters and meta query breaking?! WordPress
Hi,
We are building a complex site which loads multiple post types at one time, in this case 'tweet', 'post' and 'result'.
I want to perform the following filters -
1) where tweet 'tweet_locations' meta_key has a meta_value LIKE X or the meta_key doesn't exist
2) where result 'hide_from_latest' meta_key has a meta_value = X or the meta_key doesn't exit
3) where post 'show_on_sites' meta_key has a meta_value LIKE X (conditional, not always used)
I tried building a multi-dimensional meta_query which I believe is no supported but for some reason it causes MySQL to hang for a long time and can't perform any other requests while this is happening.
Any way around this? It's worth noting I require pagination for all of the above.
Here's the array I am passing to WP_Query -
Array
(
[post_type] => Array
(
[0] => post
[1] => tweet
[2] => result
)
[post_status] => publish
[posts_per_page] => 20
[paged] => 1
[blog_id] => %
[s] =>
[meta_query] => Array
(
[relation] => OR
[0] => Array
(
[relation] => OR
[0] => Array
(
[key] => hide_from_latest
[value] => 1
[compare] => =
)
[1] => Array
(
[key] => hide_from_latest
[value] =>
[compare] => NOT EXISTS
)
)
[1] => Array
(
[relation] => OR
[0] => Array
(
[key] => tweet_locations
[value] => :"1"
[compare] => LIKE
)
[1] => Array
(
[key] => tweet_locations
[value] =>
[compare] => NOT EXISTS
)
)
)
)
It's worth noting that even if I get rid of the multiple level meta queries and just query by the hide_from_latest conditions for example it still breaks while trying to query.
Answers (1)
Andrea P answers:
Hello
can you share the actual php where you build the args array?
nested meta queries are actually allowed:
https://make.wordpress.org/core/2014/10/20/update-on-query-improvements-in-4-1/
at the first quick look, I can tell that the value for the tweet_location first option, is possibly wrong, as it is printed like
:"1"
also, I'm not sure that you need to specify a value if you use NOT_EXISTS
and actually if you are using OR in both positions, you don't even need to nest them.. you probably can just list all the options in a first level meta query with an OR relation..
Andrea P comments:
I made a quick research and it looks like the NOT EXISTS in some cases creates issues that are often solved by specifing a random value instead of and empty string or omitting it.
https://core.trac.wordpress.org/ticket/23268
Josh Cranwell comments:
Hi,
Here is my entire file below -
// get our archive template
Post::get_template_part('pages-reusable/default-archive', null, array (
'query_args' => array (
'post_type' => array ('post', 'tweet', 'result'),
'post_status' => 'publish',
// 'posts_per_page' => 12,
'meta_query' => array (
'relation' => 'AND',
array (
'relation' => 'OR',
array (
'key' => 'hide_from_latest',
'value' => '1',
'compare' => '=',
),
array (
'key' => 'hide_from_latest',
'value' => '',
'compare' => 'NOT EXISTS',
),
),
array (
'relation' => 'AND',
array (
'key' => 'tweet_locations',
'value' => sprintf(':"%d"', get_current_blog_id()),
'compare' => 'LIKE',
),
array (
'key' => 'tweet_locations',
'value' => '',
'compare' => 'NOT EXISTS',
),
),
),
),
));
This is a custom method which passes all the data to our default-archive.php template and continues processing it (just adds pagination, any search queries etc)
<strong>Tweet Location</strong> - the meta_value of these fields is serialized data, example - a:4:{i:0;s:1:"1";i:1;s:1:"2";i:2;s:1:"3";i:3;s:1:"4";}
<strong>RE NOT_EXISTS </strong>- strictly not required but I have read somewhere that it is beneficial to include a value, this hasn't had any negative impact on my query.
<strong>OR</strong> - quite correct, the overall relation should have been AND which I have fixed.
When this query runs, it hangs in MySQL for ages with the state 'Sending Data'
Below is the query being run by WP_Query -
SELECT SQL_CALC_FOUND_ROWS wp_2_posts.ID FROM wp_2_posts
LEFT JOIN wp_2_postmeta ON ( wp_2_posts.ID = wp_2_postmeta.post_id )
LEFT JOIN wp_2_postmeta AS mt1 ON (wp_2_posts.ID = mt1.post_id AND mt1.meta_key = 'hide_from_latest' )
LEFT JOIN wp_2_postmeta AS mt2 ON ( wp_2_posts.ID = mt2.post_id )
LEFT JOIN wp_2_postmeta AS mt3 ON (wp_2_posts.ID = mt3.post_id AND mt3.meta_key = 'tweet_locations' )
WHERE 1=1 AND (
(
(
wp_2_postmeta.meta_key = 'hide_from_latest' AND CAST(wp_2_postmeta.meta_value AS CHAR) = '1'
)
OR
mt1.post_id IS NULL
)
AND
(
( mt2.meta_key = 'tweet_locations' AND CAST(mt2.meta_value AS CHAR) LIKE '%:\"2\"%' )
OR
mt3.post_id IS NULL
)
) AND wp_2_posts.post_type IN ('post', 'tweet', 'result') AND ((wp_2_posts.post_status = 'publish')) GROUP BY wp_2_posts.ID ORDER BY wp_2_posts.post_date DESC LIMIT 0, 20
Below is an EXPLAIN of the query being run -
<blockquote>1,SIMPLE,wp_2_posts,index,"PRIMARY,type_status_date,post_parent,post_author,post_name",PRIMARY,8,,1764,Using where; Using temporary; Using filesort
1,SIMPLE,wp_2_postmeta,ref,post_id,post_id,8,hondapro_core.wp_2_posts.ID,23,
1,SIMPLE,mt1,ref,"post_id,meta_key",meta_key,767,const,1,Using where
1,SIMPLE,mt2,ref,post_id,post_id,8,hondapro_core.wp_2_posts.ID,23,
1,SIMPLE,mt3,ref,"post_id,meta_key",post_id,8,hondapro_core.wp_2_posts.ID,23,Using where</blockquote>
Andrea P comments:
the args look ok.
you might try to add an actual random word as value when querying with NOT EXISTS, something like:
'value' => 'anything',
I read in that post that this is normally sorting some issues that come up when using NOT EXISTS
Josh Cranwell comments:
Hi Andrea,
I tried this but no joy - doesn't seem to have any bearing on the query either which is the thing causing the hang - hangs even if I run it myself directly against the database.
Andrea P comments:
I just seen that the last args you've posted have a relations => AND for the second part, so it searches for tweet_location to be not existent AND with value :"1"