#27043 closed defect (bug) (invalid)
tax_query limited from 3.6 to 3.7
Reported by: | AdamCapriola | Owned by: | |
---|---|---|---|
Milestone: | Priority: | normal | |
Severity: | normal | Version: | 3.8.1 |
Component: | Taxonomy | Keywords: | |
Focuses: | Cc: |
Description
A few months ago I upgraded from 3.6 to 3.7 (and now subsequently 3.8.1) and noticed on a database focused WordPress site I run that whenever I do an advanced search (via a customized form on the frontend) where I set a tax_query with more than two arguments, the search returns no results. Two or less arguments and results are returned as expected.
I've ruled out any plugins effecting this (I disabled all of them) and I can't really test on a default theme since I've done heavy customization on my site with custom taxonomies and whatnot to allow the advanced searches on the frontend, but I also tested locally on my computer, and as soon as I upgraded to 3.7 the searches with more than two tax_query arguments stopped working. The functionality worked fine through 3.6.
And to clarify, by more than two tax_query arguments I mean something like this:
add_filter( 'pre_get_posts', 'ac_filter_search' ); function ac_filter_search( $query ) { // Argument 1 $tax_query[] = array( 'taxonomy' => 'tax1', 'field' => 'slug', 'terms' => 'term1', 'operator' => 'IN' ); // Argument 2 $tax_query[] = array( 'taxonomy' => 'tax2', 'field' => 'slug', 'terms' => 'term2', 'operator' => 'IN' ); // Argument 3 $tax_query[] = array( 'taxonomy' => 'tax3', 'field' => 'slug', 'terms' => 'term3', 'operator' => 'IN' ); $query->set( 'tax_query', $tax_query ); return $query; }
I know the way search results are returned was altered in 3.7 to show more relevant results, but why would something fairly simple like this be affected?
Here are the actual var_exported $query variables for a working search and a broken one (the only differences are in tax_query).
Working: https://gist.github.com/adamcapriola/b7ae3b22e7f99e8bb3ee
Broken: https://gist.github.com/adamcapriola/cc549fc2d77a4d21b8cb
And interestly enough this following three argument tax_query does returns its expected results for some reason (I am thinking because it only has two 'IN' operators and one 'NOT IN' – the non-working example has three 'IN'):
https://gist.github.com/adamcapriola/86e0d53fe60ae1bc25e5
Any idea what's going on or what changed between 3.6 and 3.7?
Change History (8)
#3
in reply to:
↑ 2
@
11 years ago
Replying to helen:
What does "stopped working" mean? You get incorrect results, no results, no response at all? Getting any errors anywhere, perhaps something like a memory limit or even taking down MySQL? And is the generated SQL the same between the two?
For what it's worth, I've taken down MySQL going too far with tax queries that need to be transformed - worked around it by using
term_taxonomy_id
instead.
By "stopped working" I mean that no results are returned (I get the "no search results" text). Prior to 3.7, results were returned as expected. (Sorry for not being clearer about that!)
I am not getting any errors as far as I know. MySQL isn't being taken down, I don't think (that's happened to me before, but not with the queries I'm testing, which are relatively simple and have worked fine in the past). I will have to get back to you on whether the SQL being generated by the two is the same (I'll need to set up sandbox dev sites when I have some time).
Replying to wonderboymusic:
what is
WP_Query::__set_state()
?
I'm not exactly sure to be honest, that's was part of what spit out when I added var_export($query);
before return $query;
in the ac_filter_search
function.
#4
@
11 years ago
I'm not sure the best way to post this, but I set up clean development sites with 3.6.1 and 3.8.1, added my custom taxonomies, post types, and custom theme, created a few posts, and then ran the same queries on both installations with the SQL Monitor plugin active. (If there is a better way to monitor the SQL, please let me know.)
Here is where I found the biggest difference in the SQL...
WordPress 3.6.1 (3 Tax Query Arguments)
SELECT SQL_CALC_FOUND_ROWS wp_posts.ID FROM wp_posts INNER JOIN wp_term_relationships ON (wp_posts.ID = wp_term_relationships.object_id) INNER JOIN wp_term_relationships AS tt1 ON (wp_posts.ID = tt1.object_id) INNER JOIN wp_term_relationships AS tt2 ON (wp_posts.ID = tt2.object_id) INNER JOIN wp_postmeta ON (wp_posts.ID = wp_postmeta.post_id) WHERE 1=1 AND ( wp_term_relationships.term_taxonomy_id IN (13) AND tt1.term_taxonomy_id IN (4) AND tt2.term_taxonomy_id IN (5) ) AND wp_posts.post_type = 'pkmn_card' AND (wp_posts.post_status = 'publish' OR wp_posts.post_status = 'private') AND (wp_postmeta.meta_key = 'release_date_unix' ) GROUP BY wp_posts.ID ORDER BY wp_postmeta.meta_value+0 DESC LIMIT 0, 20
SELECT FOUND_ROWS()
etc...
WordPress 3.8.1 (3 Tax Query Arguments)
SELECT SQL_CALC_FOUND_ROWS wp_posts.ID FROM wp_posts INNER JOIN wp_term_relationships ON (wp_posts.ID = wp_term_relationships.object_id) INNER JOIN wp_term_relationships AS tt1 ON (wp_posts.ID = tt1.object_id) INNER JOIN wp_term_relationships AS tt2 ON (wp_posts.ID = tt2.object_id) INNER JOIN wp_postmeta ON (wp_posts.ID = wp_postmeta.post_id) WHERE 1=1 AND ( wp_term_relationships.term_taxonomy_id IN (13) AND tt1.term_taxonomy_id IN (4) AND tt2.term_taxonomy_id IN (5) ) AND (((wp_posts.post_title LIKE '% %') OR (wp_posts.post_content LIKE '% %'))) AND wp_posts.post_type = 'pkmn_card' AND (wp_posts.post_status = 'publish' OR wp_posts.post_status = 'private') AND (wp_postmeta.meta_key = 'release_date_unix' ) GROUP BY wp_posts.ID ORDER BY wp_postmeta.meta_value+0 DESC LIMIT 0, 20
No other SQL runs after that.
WordPress 3.6.1 (2 Tax Query Arguments)
SELECT SQL_CALC_FOUND_ROWS wp_posts.ID FROM wp_posts INNER JOIN wp_term_relationships ON (wp_posts.ID = wp_term_relationships.object_id) INNER JOIN wp_term_relationships AS tt1 ON (wp_posts.ID = tt1.object_id) INNER JOIN wp_postmeta ON (wp_posts.ID = wp_postmeta.post_id) WHERE 1=1 AND ( wp_term_relationships.term_taxonomy_id IN (4) AND tt1.term_taxonomy_id IN (5) ) AND wp_posts.post_type = 'pkmn_card' AND (wp_posts.post_status = 'publish' OR wp_posts.post_status = 'private') AND (wp_postmeta.meta_key = 'release_date_unix' ) GROUP BY wp_posts.ID ORDER BY wp_postmeta.meta_value+0 DESC LIMIT 0, 20
SELECT FOUND_ROWS()
etc...
WordPress 3.8.1 (2 Tax Query Arguments)
SELECT SQL_CALC_FOUND_ROWS wp_posts.ID FROM wp_posts INNER JOIN wp_term_relationships ON (wp_posts.ID = wp_term_relationships.object_id) INNER JOIN wp_term_relationships AS tt1 ON (wp_posts.ID = tt1.object_id) INNER JOIN wp_postmeta ON (wp_posts.ID = wp_postmeta.post_id) WHERE 1=1 AND ( wp_term_relationships.term_taxonomy_id IN (4) AND tt1.term_taxonomy_id IN (5) ) AND (((wp_posts.post_title LIKE '% %') OR (wp_posts.post_content LIKE '% %'))) AND wp_posts.post_type = 'pkmn_card' AND (wp_posts.post_status = 'publish' OR wp_posts.post_status = 'private') AND (wp_postmeta.meta_key = 'release_date_unix' ) GROUP BY wp_posts.ID ORDER BY wp_postmeta.meta_value+0 DESC LIMIT 0, 20
SELECT FOUND_ROWS()
etc...
AND (((wp_posts.post_title LIKE '% %') OR (wp_posts.post_content LIKE '% %')))
is the only difference between both, but for whatever reason kills the query when there are three or more tax_query arguments; two or less and it has no effect.
So, now knowing that's what changed after WordPress 3.6.1, I tried adding a string to the s
argument of the query to match part of a post title, and I was able to get results to return in WordPress 3.8.1 with three or more tax_query arguments.
Perhaps there can be some kind of a check, so that if s
is empty then that part doesn't get added to the SQL? I think that's what throwing it off.
What does "stopped working" mean? You get incorrect results, no results, no response at all? Getting any errors anywhere, perhaps something like a memory limit or even taking down MySQL? And is the generated SQL the same between the two?
For what it's worth, I've taken down MySQL going too far with tax queries that need to be transformed - worked around it by using
term_taxonomy_id
instead.