Advanced multi-taxonomy WP_Query()s

[markjaquith]

We have this great taxonomy system, but we're only able to query one of them at a time. There is no way to ask for posts that are in the 'cars' category but lack the "VW Beetle" tag. What people end up doing is crafting complex category systems to simulate multiple taxonomies. That's lame. We should make them want to use multiple taxonomies. Let's give them the tools to do it.

Proposed syntax, and half-working example implementation:

<?php

class WP_Query_Tax_Or {
	var $contents = array();
	function WP_Query_Tax_Or( $array ) {
		$this->contents = $array;
		return $this;
	}
}

class WP_Query_Tax_And {
	var $contents = array();
	function WP_Query_Tax_And( $array ) {
		$this->contents = $array;
		return $this;
	}
}

class WP_Query_Tax {
	var $taxonomy;
	var $object;
	var $ID;
	function WP_Query_Tax( $taxonomy, $object ) {
		$this->taxonomy = $taxonomy;
		$this->object = $object;
		$this->ID = wp_some_function_to_get_an_ID_from_ID_or_name_or_slug( $taxonomy, $object );
		return $this;
	}
}

class WP_Query_Tax_Not {
	var $taxonomy;
	var $object;
	var $ID;
	function WP_Query_Tax_Not( $taxonomy, $object ) {
		$this->taxonomy = $taxonomy;
		$this->object = $object;
		$this->ID = wp_some_function_to_get_an_ID_from_ID_or_name_or_slug( $taxonomy, $object );
		return $this;
	}
}


function wp_tax_and() {
	return new WP_Query_Tax_And( func_get_args() );
}

function wp_tax_or() {
	return new WP_Query_Tax_Or( func_get_args() );
}

function wp_tax( $taxonomy, $object ) {
	return new WP_Query_Tax( $taxonomy, $object );
}

function wp_tax_not( $taxonomy, $object ) {
	return new WP_Query_Tax_Not( $taxonomy, $object );
}

function wp_some_function_to_get_an_ID_from_ID_or_name_or_slug( $taxonomy, $object ) {
	return $object;
}

$array = array(
	'tax' => wp_tax_and(
		wp_tax_or(
			wp_tax( 'post_category', 59 ),
			wp_tax( 'post_tag', 'web' )
		),
		wp_tax_not( 'post_category', 24 ),
		wp_tax_not( 'post_tag', 'wordpress' )
	)
);

var_dump( $array );

Simple enough. group with wp_tax_and() or wp_tax_or(). Select a taxonomy item using wp_tax( 'taxonomy', 'ID or name or slug' ) and omit a taxonomy item using wp_tax_not( 'taxonomy', 'ID or name or slug' ).

If you run the example code, you'll get an array of objects that could be iterated by WP_Query code using subqueries and parenthesis to generate the precise query that was requested.

It'll translate directly into SQL. Start of AND or OR means you start parenthesis, and join all children elements with AND or OR as appropriate. Getting to the end of an AND or OR means you close parenthesis. And then just use IN() and NOT IN() for wp_tax() and wp_tax_not() respectively.

[markjaquith]

Related (less ambitious): #9951

[scribu]

I'm trying to achieve the same thing with the ​Query Multiple Taxonomies plugin. It progressively filters posts using multiple queries.

I'm a bit worried about having it all in a single query: Will it scale, given all the subqueries and joins that would be required? (Of course, this could be rigorously tested once we have an implementation)

[markjaquith]

scribu — it'd use subqueries, which shouldn't be slower than multiple queries. It wouldn't be as fast as querying a single taxonomy, for sure. If I were using it, I'd cache the result. But MySQL is rarely the WP bottleneck. I think it could work.

[scribu]

Replying to markjaquith:

I was thinking about "MySQL server went away" type of errors.

Anyway, I like the potential of this proposed API.

[scribu]

delayed parsing

[scribu]

I've added two sample implementations:

• eager.php: each function generates the SQL code as soon as it's called

• lazy.php: each function creates a new object; the sql can be generated at a later point.

The lazy parsing implementation would only be useful if it would first analyze all the clauses and then output optimized SQL.

On that note, instead of wp_some_function_to_get_an_ID_from_ID_or_name_or_slug(), I think it would be better if wp_tax() accepted a third $field argument, to avoid doing extra queries.

[scribu]

generate SQL string directly

[scribu]

corrected SQL

[scribu]

Related: #7463

[filosofo]

I thought of this ticket while reading ​this post, because basically what this proposed system attempts to do is put an ORM on top of the taxonomy and post database queries.

One (worthy) goal is obviously to provide an abstraction for complex taxonomy queries, an abstraction that's independent of the actual database structure. The problem is that it ends up forcing developers to learn a new, custom querying syntax that isn't as powerful as basic SQL.

I don't have a good solution at this point. Although we don't want to expose the actual database structure, I don't think we can get away from SQL-like syntax because it's really the most familiar and appropriate tool for the job.

Facebook Query Language (FQL) is an example of how you can expose an SQL-like API, so perhaps like it we could offer a few reduced SELECT query patterns that are trivial to parse. The idea would be that it is a syntax already known to those who need to make the complex queries in the first place, but yet a syntax not coupled to the actual, current database structure.

[markjaquith]

I will bless and take responsibility for this one.

[mikeschinkel]

Replying to filosofo:

I thought of this ticket while reading ​this post, because basically what this proposed system attempts to do is put an ORM on top of the taxonomy and post database queries.

One (worthy) goal is obviously to provide an abstraction for complex taxonomy queries, an abstraction that's independent of the actual database structure. The problem is that it ends up forcing developers to learn a new, custom querying syntax that isn't as powerful as basic SQL.

I don't have a good solution at this point. Although we don't want to expose the actual database structure, I don't think we can get away from SQL-like syntax because it's really the most familiar and appropriate tool for the job.

Facebook Query Language (FQL) is an example of how you can expose an SQL-like API, so perhaps like it we could offer a few reduced SELECT query patterns that are trivial to parse. The idea would be that it is a syntax already known to those who need to make the complex queries in the first place, but yet a syntax not coupled to the actual, current database structure.

+1. I echo filosofo's concerns and really like his suggestions.

[scribu]

I think I have something that will satisfy 90% of the use cases. While working on #14572, I introduced a helper function: _wp_meta_sql(). It takes a list of meta queries.

A single meta query looks like this:

array(
  'meta_key' => 'foo',
  'meta_value' => 'bar',
  'meta_compare' => '!=',
)

I think we can have something similar for taxonomies, which would cover 90% of the use cases:

array(
  'taxonomy' => 'post_tag',
  'terms' => array('green', 'blue'),
  'operator' => 'IN',
)

where 'operator' can be 'IN', 'NOT IN', 'AND' etc.

WP_Query would be enhanced to recognize multiple taxonomy => value pairs.

Also, there could be a new 'taxonomy_query' var that would accept a list of taxonomy queries.

[scribu]

So, simple usage:

query_posts( array(
  'tag' => 'foobar',
  'color' => 'green'
) );

Advanced usage:

query_posts( array(
  'taxonomy_query' => array(
    'taxonomy' => 'post_tag',
    'terms' => array( 'foobar' ),
  ), array(
    'taxonomy' => 'color',
    'terms' => array( 'green', 'white' ),
    'operator' => 'AND'
  )
) );

[mikeschinkel]

Replying to scribu:

So, simple usage:

query_posts( array(
  'tag' => 'foobar',
  'color' => 'green'
) );

Advanced usage:

query_posts( array(
  'taxonomy_query' => array(
    'taxonomy' => 'post_tag',
    'terms' => array( 'foobar' ),
  ), array(
    'taxonomy' => 'color',
    'terms' => array( 'green', 'white' ),
    'operator' => 'AND'
  )
) );

How to handle complex OR, especially when post_meta needs to be considered, i.e. in pseudo-SQL

WHERE 
   (META team = Bulldogs AND (TERM color IN red AND black) 
      OR 
   (META team = Yellow Jackets AND (TERM color IN white AND gold)

?

[scribu]

You can't, but then again you will never be able to do everything using only the API, unless the API uses ORM, which you seem to be against.

[mikeschinkel]

Replying to scribu:

You can't, but then again you will never be able to do everything using only the API, unless the API uses ORM, which you seem to be against.

Why put words in my mouth from a simple +1? And never say never; I'm sure it's possible with a well thought-out design.

[scribu]

Be more careful where you put your +1s next time. :P

Joking aside, I believe having a working solution in 3.1 is better than maybe having an ideal solution in a future release.

[scribu]

I mean, this ticket has been open for 5 months, with an even older one open for 16 months. It's time we ship something.

[mikeschinkel]

Replying to scribu:

I believe having a working solution in 3.1 is better than maybe having an ideal solution in a future release.
I mean, this ticket has been open for 5 months, with an even older one open for 16 months. It's time we ship something.

Fair point, and agreed.

But realize it will just push the limits to different point; i.e there will still be people needing more.

Something to consider for v3.2 or later; a specialized SQL-like WP-specific query language that can handle anything SQL can handle, have it's own QOM (Query Object Mode) like HTML5 has it's own DOM, and be much simpler given it recognizes the underlying patterns in WordPress and would ultimately map to MySQL (or maybe SQLite or even a NoSQL variant.) Envision queries like:

LIST posts WITH TAG featured
LIST posts WITH TAXONOMY color TERMS red,green,blue
LIST posts WITH TYPE event SORTED BY title
LIST posts DURING 2010 REVERSE SORTED BY date
LIST posts CONTAINING "WordPress Plugins"
GET post IDENTIFIED BY 37
GET post ID 37
GET page IDENTIFIED BY about
GET page SLUG about
LIST postfields WHERE post IDENTIFIED BY 37
LIST postfields WHERE posts WITH TAG featured
LIST posts WITH TAGS GROUP(featured AND issues) BUT NOT politics

The design is to be approachable by mere mortals and to avoid much of what make SQL appear to be arcane to most people.
I've already written much of the EBNF for it. And yes, it would start life as a plugin.

-Mike

[scribu]

12891.diff is a start in abstracting away the taxonomy SQL generation out of WP_Query.

[scribu]

Start to pull tax SQL out of WP_Query and into _wp_tax_sql()

[markjaquith]

Scribu, I'd really like to be able to determine the operator between taxonomies, and be able to group terms. Is there something about my proposed syntax that you objected to?

As two minor enhancements, I'd take your advice about id/slug and make explicit wrappers. wp_tax_id(), wp_tax_slug() and their "not" counterparts. I'd also introduce specification of hierarchy handling, for hierarchical taxonomies. Maybe _but_no_children suffixes to avoid the default behavior of querying for all children.

filosofo's suggestion of something like FQL (WPQL?) has appeal. You could do all of things I want to do, with that. You might need something to say "_but_no_children" for hierarchical taxonomies, but otherwise, it translates well to a simplified SQL-like language. I'm just not sure we're ready for that — it's a big step.

[mikeschinkel]

Replying to markjaquith:

filosofo's suggestion of something like FQL (WPQL?) has appeal. You could do all of things I want to do, with that. You might need something to say "_but_no_children" for hierarchical taxonomies, but otherwise, it translates well to a simplified SQL-like language. I'm just not sure we're ready for that — it's a big step.

A "WPQL" was exactly what I was showing a hypothetical for above but agree that it's a big step. It would take quite a while to get it right including having lots of people throwing advanced use-cases at it to make sure its design was robust before unleashing it on the world.

v3.5? ;-)

[scribu]

Scribu, I'd really like to be able to determine the operator between taxonomies, and be able to group terms. Is there something about my proposed syntax that you objected to?

The main problem I have with it is that it's akward to build a query progressively. Here is some code from my Query Multiple Taxonomies plugin:

	$query = array();
	foreach ( $wp_query->_qmt_query as $taxname => $value )
		foreach ( explode( '+', $value ) as $value )
			$query[] = wp_tax( $taxname, explode( ',', $value ), 'slug' );

	$post_ids = $wpdb->get_col( wp_tax_query( wp_tax_group( 'AND', $query ) ) );

You tell me if it's readable or not.

[scribu]

Anyway, regardless if we're going to use this syntax or not, we still need to clean up WP_Query first, so I'll just continue working on 12891.diff

[scribu]

12891.2.diff: all taxonomy combinations supported: query categories, tags and custom taxonomies in one go.

[scribu]

Multiple taxonomy queries supported

[scribu]

Introduce wp_tax_query() and use it in get_objects_in_term()

[scribu]

I've found that making get_objects_in_term() more flexible is the organic way to extend the taxonomy API.

I've also added a wp_tax_query() function on top of it. See ​12891.4.diff

[scribu]

Extend get_objects_in_term() and use it in wp_tax_query() instead

[scribu]

Related: #14831

[scribu]

(In [15613]) Clean up taxonomy queries in WP_Query. See #12891

[scribu]

Went ahead with the spring cleaning in WP_Query. I expect there will be a lot of obscure side effects, so I wanted to get a head start before the beta period.

[mikeschinkel]

What's the chance of adding a callback filter to the $args for WP_Query() that would be called after the posts_request filter so that we don't have to depend on global filters when we need to modify a query for a specific call in a plugin? (Ideally this callback would work for all the filters in WP_Query() but I'd settle for one that gets called after posts_request vs. not having any.)

[automattor]

(In [15615]) Calculate term count using $wp_query->tax_query. See #12891

[scribu]

Mike, that's outside the scope of this ticket. Please open a new one.

[scribu]

Related: #12659

[mikeschinkel]

Replying to scribu:

Mike, that's outside the scope of this ticket. Please open a new one.

#14869

[automattor]

(In [15625]) Remove extraneous sanitization of category_name. Leave it to wp_tax_query(). See #12891

[scribu]

Marked #14699 as dup.

[scribu]

(In [15649]) Set $post_type to 'any' when taxonomies are involved (code resqued from r15613). See #12891

[dd32]

Some things I've noticed

• Viewing Term pages which have no objects within will display a DB Error ( SQL IN() Can't be empty )
• Viewing parent Terms (Categories, and a Custom Taxonomy) will not include the child term's objects(posts). This was definitely the case with categories previously, but am unsure of what the situation with custom tax's were.

[dd32]

(In [15652]) Do not generate invalid SQL for empty term archives. See #12891

[scribu]

Replying to dd32:

• Viewing parent Terms (Categories, and a Custom Taxonomy) will not include the child term's objects(posts). This was definitely the case with categories previously, but am unsure of what the situation with custom tax's were.

Not true. It's taken care of in get_objects_in_term().

[scribu]

Or at least it was. Looking into it now.

[dd32]

It's taken care of in get_objects_in_term().

You may want to thoroughly test it then.

get_objects_in_term() handles it for requests where its being queried by a term_id, In the case of pretty permalinks, where its being queried by slug, They are not.

[scribu]

(In [15654]) get_objects_in_term(): include term children when querying by slug. See #12891

[scribu]

Related: #15007

[scribu]

Related: #15032

[scribu]

(In [15731]) Generalize taxonomy queries:

• transform wp_tax_query() into WP_Object_Query::get_tax_sql()
• create parse_tax_query() method in WP_Query
• add doc-block for $tax_query and $meta_query

See #15032. See #12891.

[scribu]

(In [15733]) Better way of settings tax related query flags. See #12891

[dd32]

Receiving this after SVN'uping this evening:

WordPress database error: [You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ')' at line 5]
SELECT object_id FROM wp_term_relationships INNER JOIN wp_term_taxonomy USING (term_taxonomy_id) WHERE taxonomy IN ('state') AND term_id IN ()

when viewing a taxonomy archive which doesnt exist (ie. the term doesnt exist, /state/404-me-please/)

[scribu]

(In [15734]) Prevent db error when dealing with unexistant parent term. See #12891

[markjaquith]

Let's go with something closer to what scribu suggested. Let's skip parentheticals this time around, and assume the various taxonomy queries are ANDed together. So, taxonomy, term(s) and an operator to join those terms. Scribu, want to take first shot at that, since you suggested the syntax?

[scribu]

(In [15751]) Add 'tax_query' as public query var. See #12891

[scribu]

(In [15752]) Remove debug cruft introduced by [15751]. See #12891

[scribu]

Done. Advanced query example updated:

get_posts( array(
  'post_type' => 'event',
  'tax_query' => array(
    array(
      'taxonomy' => 'location',
      'terms' => array( 'everywhere' )
    ),
    array(
      'taxonomy' => 'location',
      'terms' => array( 'there' ),
      'operator' => 'NOT IN'
    ),
  )
) );

[scribu]

Maybe we should replace 'IN' / 'NOT IN' with 'include' / 'exclude'.

[mikeschinkel]

Replying to scribu:

Done. Advanced query example updated:

Nice! But would 'tax_terms' not be more consistent with other arguments than 'tax_query'?

[scribu]

No, because it spans multiple taxonomies. Also, it's consistent with meta_query.

[scribu]

(In [15765]) Get rid of redundant ->tax_query. See #12891

[scribu]

(In [15773]) don't re-urlencode taxonomy query vars. See #12891

[hakre]

Last patch might have permalink compability implications.

[scribu]

It does, but I think we should handle it in the WP class or in WP_Rewrite. Related: #9591

[scribu]

(In [15850]) Re-add the ability to filter by 'taxonomy' and 'term' query vars. Fixes #13582. See #12891

[automattor]

(In [15860]) Fix 'taxonomy' and 'term' query var logic. See #12891

[scribu]

(In [15861]) Get rid of $taxonomy_query_vars altogether. See #12891

[Utkarsh]

'cat' query var is set to a blank string on category archives page. Patch attached.

[scribu]

(In [16116]) Remove check on unexistant $ids var in WP_Query::get_posts. Props Utkarsh. See #12891

[ramenboy]

The change in [15613] makes post queries such as get_posts('tag=test') no longer filter on tags if the SimpleTags plugin is enabled.

Steps to reproduce:

1. Using a fresh WP trunk checkout, create a second post with a tag of "test"
2. Do a print_r(get_posts('tag=test')) from index.php - only one post is displayed.
3. Download and active the SimpleTags plugin from ​http://wordpress.org/extend/plugins/simple-tags/
4. Reload index.php, both posts are now displayed
5. The problem goes away if SimpleTags is deactivated or if you roll back this change, ie. it exists with "svn up -r 15613" and goes away with "svn up -r 15612"

[scribu]

Turns out it was a problem with the plugin.

[ramenboy]

I reported the above issue to the Simple Tags author:

​http://wordpress.org/support/topic/simple-tags-breaks-tag-based-post-queries-in-wp-trunk

[nacin]

Where did the taxonomy and term query vars go?

They're not getting set properly in the case of querying a custom taxonomy page, and it is causing errors in wp_title(), in the is_tax() branch.

[scribu]

(In [16258]) Set 'operator' field. Fixes #15350. See #12891

[scribu]

They're not getting set properly in the case of querying a custom taxonomy page, and it is causing errors in wp_title(), in the is_tax() branch.

Steps to reproduce?

[scribu]

Related: #15363

[scribu]

Also related: #14880 #15161

[dd32]

They're not getting set properly in the case of querying a custom taxonomy page, and it is causing errors in wp_title(), in the is_tax() branch.

Steps to reproduce?

View a custom Taxonomy index page with WP_DEBUG enabled, Take note of the page title containing PHP Notice's.

I mentioned the lack of taxonomy/term query vars in a previous ticket 2+ weeks back, I cant find it right now, But I believe you agree'd that adding it was needed for backwards-compat at least (back-compat isn't the only issue here however).

[dd32]

I mentioned the lack of taxonomy/term query vars in a previous ticket 2+ weeks back, I cant find it right now

Comment: ​http://core.trac.wordpress.org/ticket/12659#comment:25

[Viper007Bond]

Minor error in phpdoc: #15431

Also get_tax_sql() needs better error handling. It can fatal error out if a non-existent taxonomy name is passed. See incoming patch for fix for that...

[scribu]

(In [16379]) Better error handling in get_meta_sql(). Props Viper007Bond. See #12891

[ryan]

Sites that perform well with 3.0 fall over with very slow queries like the following when running 3.1:

SELECT object_id
FROM wp_term_relationships
WHERE 1 =1
AND object_id
IN (

SELECT object_id
FROM wp_term_relationships
INNER JOIN wp_term_taxonomy
USING ( term_taxonomy_id )
INNER JOIN wp_terms
USING ( term_id )
WHERE taxonomy
IN (
'post_tag'
)
AND slug
IN (
'chevy-volt', 'fisker', 'ford', 'gm', 'karma', 'leaf', 'nissan', 'roadster', 'tesla', 'v-vehicle', 'volt'
)
)
AND object_id
IN (

SELECT object_id
FROM wp_term_relationships
INNER JOIN wp_term_taxonomy
USING ( term_taxonomy_id )
INNER JOIN wp_terms
USING ( term_id )
WHERE taxonomy
IN (
'primary_channel'
)
AND slug
IN (
'all'
)
)

That takes over a minute.

[ryan]

MySQL returned an empty result set (i.e. zero rows). (Query took 61.8451 sec)

Ouch!

[ryan]

Could be a plugin incompatibility. I think we'll see several of those, alas.

[ryan]

In this case, WP might be acting on query vars too early, before filters have had a chance to do their thing. Still investigating.

[ryan]

Looks like the plugins is hooking onto parse_query and looking for the primary_channel query var to be set to all. If it is set to all then the primary_channel query var as well as the term and taxonomy query vars are set to null so that they won't be a part of the query later. This is breaking down somewhere in 3.1.

[scribu]

I thought I fixed such cases with [16380].

[ryan]

I'm dumping the query vars and it does seem like that is fixed. Still trying to track it down. Sorry for the noise.

[ryan]

Fixed the plugin. I'm not really sure how it was working under 3.0. I don't think there's anything here we need to defend against back compat wise.

[ryan]

WP_Query('posts_per_page=15&cat=-21072,-37613,-4533907');

Generates a query like:

SELECT SQL_CALC_FOUND_ROWS  wp_3508545_posts.* FROM wp_3508545_posts  WHERE 1=1  AND wp_3508545_posts.ID IN(0, 14, 17, 25

Where the post ID list has 1666871 IDs and the resulting query is 1.2 million characters long. This causes segfaults.

[ryan]

The analagous query in 3.0 is:

SELECT * FROM wp_3508545_posts WHERE ID IN(233413,232570,233293,232774,233148,233135,232474,233138,40895,230938,40891,225264,42561,41262,41460)

[ryan]

get_tax_sql() does this:

SELECT object_id
FROM wp_trunk_term_relationships
INNER JOIN wp_trunk_term_taxonomy USING (term_taxonomy_id)
WHERE taxonomy IN ('category')
AND term_id NOT IN (21072,37613,4533907)

That query is unlimited despite the original request being for only 15 posts. This won't scale.

[ryan]

get_tax_sql() has to go, it seems. It runs before any of the orderby and limit args are processed. I think we have to pare this back.

[nacin]

Bottom up approach started with 3.0 query.php and added changesets. Missed pieces of [15649], [15650] (did those get reverted); [15732] [15824] [15825] some of [15827] (hierarchical URLs); [15773] (urlencode); [15795] (pieces filter); [15923] then [16155] (first hunk failed); [15887] (I don't see what this is patching, most was reverted in [15888]); [15982] ('fields' arg).

[scribu]

Would it be better if get_tax_sql() returned the raw subqueries instead of the resulting post ids?

[scribu]

Or we could do it more similarly to get_meta_sql(), generating JOIN clauses besides WHERE.

[nacin]

Replying to scribu:

Or we could do it more similarly to get_meta_sql(), generating JOIN clauses besides WHERE.

A big problem with get_meta_sql() is it lowers the barrier for entry. I liked that I needed to roll my own JOIN clauses because if I was capable of doing that, then I was capable of realizing the bad queries that could come out of it.

Being able to blindly specify any number of meta key queries is going to result in abuse.

[scribu]

(In [16402]) Don't pollute the 'tax_query' query var. See #12891

[ryan]

I think avoiding subqueries in WHERE IN clauses is considered good practice with MySQL. JOINs would probably be better.

[scribu]

(In [16403]) Revert taxonomy.php part of [16402]. Not fully backed yet. See #12891

[scribu]

(In [16404]) Revert [16402] outright. Causes issues with themes. See #12891

[nacin]

query.php.r15471.2.diff​ starts with r15471 as a base, builds query.php back up without and taxonomy or metadata query changes. I can compile a list of changesets that it reverts and tickets it re-opens.

The following changesets were skipped. They'll need to be rewritten as appropriate:

• [15732] [15824] [15825] some of [15827] (hierarchical URLs)
• [15773] (urlencode)
• [15795] (pieces filter); #14997 is still open so we can re-do it
• [15923] followed by [16155] (only the first hunk failed)

[scribu]

​scale.12891.diff:

Queries for existing term ids first.

This avoids the scaling problem when doing something like cat=-123456, which will have no effect (and therefore no performance penalty) if that category doesn't exist.

[scribu]

Also, get_objects_in_term() was reverted to it's 3.0 state.

[scribu]

Replying to ryan:

That query is unlimited despite the original request being for only 15 posts. This won't scale.

We can't limit the IN() to 15 ids because that would give incorrect results. The first 15 matched IDs might not match the outer query, like post_type etc.

I see 3.0 was using GROUP BY to handle intersections so I think we can use that in get_tax_sql() too.

[scribu]

Optimized subqueries

[scribu]

​scale.12891.2.diff:

Each tax_query generates a subquery. It might not be the fastest way, but at least it won't segfault.

[scribu]

A simple foo=bar query executes in 0.10 seconds with 22.000 posts, matching 113 of them:

SELECT SQL_CALC_FOUND_ROWS  wp_posts.* FROM wp_posts  WHERE 1=1  AND wp_posts.ID IN (
			SELECT object_id
			FROM wp_term_relationships
			INNER JOIN wp_term_taxonomy USING (term_taxonomy_id)
			WHERE taxonomy IN ('foo')
			AND term_id IN (31)
		) AND wp_posts.post_type = 'post' AND (wp_posts.post_status = 'publish' OR wp_posts.post_status = 'future' OR wp_posts.post_status = 'draft' OR wp_posts.post_status = 'pending' OR wp_posts.post_status = 'private')  ORDER BY wp_posts.post_date DESC LIMIT 0, 100

[nacin]

​http://jonahellison.com/mysql-subqueries-optimization-and-using-update-inner-joins

[scribu]

That post isn't very thorough.

[ryan]

Testing that patch. Is this necessary:

SELECT term_id
FROM wp_3508545_term_taxonomy
WHERE taxonomy IN ('category')
AND term_id IN (21072,37613,4533907) 

Can we assume that the requested terms are in the request taxonomy?

[ryan]

The blog that created that crazy cat exclusion query is much better now. It runs in about 2.5 seconds. I don't know how that compares to 3.0 yet, but it is an improvement from a 1.2 million char query string. :-)

[scribu]

Can we assume that the requested terms are in the request taxonomy?

Only if I fetch the term_taxonomy_ids instead of the term ids. That would also remove an extra join in each subquery. I'll write it up.

[ryan]

In 3.1 this query takes 2,417.7ms.

SELECT SQL_CALC_FOUND_ROWS wp_3508545_posts.* FROM wp_3508545_posts WHERE 1=1 AND wp_3508545_posts.ID IN (
SELECT object_id
FROM wp_3508545_term_relationships
INNER JOIN wp_3508545_term_taxonomy USING (term_taxonomy_id)
WHERE taxonomy IN ('category')
AND term_id IN (14667805,14667940,14667953,14667963,34584120,1769037)
) AND wp_3508545_posts.post_type IN ('post', 'page', 'attachment') AND (wp_3508545_posts.post_status = 'publish' OR wp_3508545_posts.post_status = 'private') ORDER BY wp_3508545_posts.post_date DESC LIMIT 0, 6; SELECT FOUND_ROWS()

In 3.0 the analogous query takes 178.5ms:

SELECT SQL_CALC_FOUND_ROWS wp_3508545_posts.* FROM wp_3508545_posts INNER JOIN wp_3508545_term_relationships ON (wp_3508545_posts.ID = wp_3508545_term_relationships.object_id) INNER JOIN wp_3508545_term_taxonomy ON (wp_3508545_term_relationships.term_taxonomy_id = wp_3508545_term_taxonomy.term_taxonomy_id) WHERE 1=1 AND wp_3508545_term_taxonomy.taxonomy = 'category' AND wp_3508545_term_taxonomy.term_id IN ('1769037', '14667805', '14667940', '14667953', '14667963', '34584120') AND wp_3508545_posts.post_type = 'post' AND (wp_3508545_posts.post_status = 'publish' OR wp_3508545_posts.post_status = 'private') GROUP BY wp_3508545_posts.ID ORDER BY wp_3508545_posts.post_date DESC LIMIT 0, 6; SELECT FOUND_ROWS()

[ryan]

The unbounded subqueries kill performance.

[ryan]

scale.12891.3.diff performs about the same as .2.

[scribu]

Ok, I'll make it use JOINs, just like in get_meta_sql().

[scribu]

Use term_taxonomy_id directly

[scribu]

Use JOINs

[scribu]

scale.12891.4.diff should be even a little faster than 3.0.

[ryan]

It is faster than 3.0 except for excludes, which are very slow.

SELECT SQL_CALC_FOUND_ROWS wp_3508545_posts.* FROM wp_3508545_posts
INNER JOIN wp_3508545_term_relationships ON (wp_3508545_posts.ID = wp_3508545_term_relationships.object_id) WHERE 1=1 AND wp_3508545_term_relationships.term_taxonomy_id NOT IN (16,38415,45884,44849) AND wp_3508545_posts.post_type IN ('post', 'page', 'attachment') AND (wp_3508545_posts.post_status = 'publish' OR wp_3508545_posts.post_status = 'private') ORDER BY wp_3508545_posts.post_date DESC LIMIT 0, 15

That took 35 seconds.

[scribu]

Use JOINs + avoid extra query from 'term_id' to 'term_id'

[scribu]

Use JOIN for IN and subquery for NOT IN

[ryan]

The exclusion is much faster now. 1.6s vs 35s, which is comparable to 3.0. The inclusions are slightly faster than 3.0.

[scribu]

Yay, so we go with scale.12891.5.diff ?

[ryan]

Commit, and we'll see how it goes. Thanks for making this happen. I was not optimistic this morning. :-)

[scribu]

(In [16413]) Optimize get_tax_sql(). See #12891

[scribu]

(In [16414]) Re-introduce ->tax_query as a read-only var. See #12891

[tott]

Ran into some errors with the generated query.

WordPress database error Column 'term_id' in field list is ambiguous for query made by _transform_term

Attached patch attachment:ticket:12891:taxonomy.diff​ should fix this.

[tott]

Prepend table names to resulting field params for _transform_terms

[scribu]

(In [16423]) Fix ambiguous column error. See #12891

[ryan]

Since the decision to switch away from NOT IN to a subquery for exclusions happened in IRC, let me fill in the background. NOT IN was scanning the whole table (almost 200k rows). The same query without the "NOT" scanned only 5k rows. The unbounded subquery we switched to for exclusions is much faster, as mentioned above.

[foofy]

The subquery for NOT IN added in r12891 is not quite right as it includes posts with the excluded terms so long as the post has other terms that are not excluded. I moved the NOT in front of the subquery and MySQL shows that either way it scans the same number of rows.

$where .= " AND $primary_table.$primary_id_column NOT IN (
	SELECT object_id
	FROM $wpdb->term_relationships
	WHERE term_taxonomy_id IN ($terms)
)";

[scribu]

Put NOT IN outside of subquery

[scribu]

ryan, could you confirm that scale.12891.6.diff doesn't degrade performance?

[markjaquith]

Updated patch (old one had conflicts).

[ryan]

Seems to perform about the same.

[markjaquith]

(In [16467]) Exclude term exclusions even if they are also inclusions. props foofy, scribu. see #12891

[ryan]

SELECT SQL_CALC_FOUND_ROWS wp_14578484_posts.* FROM wp_14578484_posts INNER JOIN wp_14578484_term_relationships ON (wp_14578484_posts.ID = wp_14578484_term_relationships.object_id) WHERE 1=1 AND wp_14578484_posts.ID NOT IN (104754,104534,104560,104705) AND wp_14578484_term_relationships.term_taxonomy_id IN (1194,11561,12084,1202,1203,1199,1196,1200,1197,1201,1204,5016,6745,6950,6744,6558,1195,1198,6746,13187) AND wp_14578484_posts.post_type IN ('post', 'page', 'attachment', 'calendar_event', 'listicle', 'background', 'top-list', 'guide', 'gallery', 'lead', 'personality', 'show', 'station', 'breaking') AND (wp_14578484_posts.post_status = 'publish' OR wp_14578484_posts.post_status = 'private') ORDER BY wp_14578484_posts.post_date DESC LIMIT 0, 15

That query returns duplicate posts. If a post belongs to more than one of the terms in the IN() then the post will be duplicated.

Query created with this call:

query_posts(array('category_name'=> 'a_slug', 'post__not_in'=>$exclude_ids, 'paged'=>get_query_var('paged')))

Also notice all of those post types. In 3.0 only the 'post' post_type is queried. That could be unrelated to this ticket though.

[ryan]

We might have to bring that extra join back.

[ryan]

Ah, we lost the GROUP BY. Adding that back in fixes it.

[scribu]

(In [16482]) Force GROUP BY when there's a tax query. See #12891

[ryan]

I thought I committed a fix for this last night. This morning I noticed I had the svn ci sitting on the command line but I never hit enter. :-)

[chrisscott]

[15649] breaks existing behavior by setting post_type to 'any' instead of defaulting to 'post' when a taxonomy is specified but post_type is not. We're already being bitten by this on VIP sites on .com.

[Otto42]

Is there no way to do an OR query with this? I can't select posts in the gallery category OR in the gallery post_format?

[Otto42]

When trying to create a query to do what I want (namely gallery-category OR gallery-post_format), I eventually came up with this:
`query_posts(array(

'tax_query' => array(

array(

'taxonomy' => 'post_format',
'terms' => array('post-format-gallery'),
'field' => 'slug',

),
array(

'taxonomy' => 'category',
'terms' => array('gallery'),
'field' => 'slug',

),

)

) );
`

But of course can find no way to make that an OR relationship. In looking at the resulting query this produces (eventually), I have this:

SELECT SQL_CALC_FOUND_ROWS wp_posts.* 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) WHERE 1=1 AND wp_term_relationships.term_taxonomy_id IN (110) AND tt1.term_taxonomy_id IN (11) AND wp_posts.post_type IN ('post', 'page', 'attachment') AND (wp_posts.post_status = 'publish' OR wp_posts.post_author = 1 AND wp_posts.post_status = 'private') GROUP BY wp_posts.ID ORDER BY wp_posts.post_date DESC LIMIT 0, 10

That naturally produces zero rows, because it's assuming an AND relationship. The key part of that turns out to be here:
wp_term_relationships.term_taxonomy_id IN (110) AND tt1.term_taxonomy_id IN (11)

Simply changing that AND to an OR and grouping them in parentheses gets me the results I want.

So perhaps we can add the ability of grouping to this, somehow? If I was to group the two things I wanted OR'ed into their own array with some kind of extra parameter to specify an OR relationship between them? Something like this:

`query_posts(array(

'tax_query' => array(

array('relationship'=>'OR',

array(

'taxonomy' => 'post_format',
'terms' => array('post-format-gallery'),
'field' => 'slug',

),
array(

'taxonomy' => 'category',
'terms' => array('gallery'),
'field' => 'slug',

),

);

)

) );
`

Or something? This array nesting is bloody complicated though, there has to be an easier way to allow this sort of thing.

[Otto42]

get-tax-or.diff is a working example of the proposal I made earlier. It uses get_tax_sql recursively for grouped cases that have a relationship defined between them.

I have not tested it extensively, I suggest somebody look it over and rethink what I probably missed.

[ryan]

WP_Query( array( 'category_name' => 'A Cat', 'showposts' => '1' ) );

Produces the following assortment of queries:

SELECT wp_trunk_term_taxonomy.term_id
FROM wp_trunk_term_taxonomy
INNER JOIN wp_trunk_terms USING (term_id)
WHERE taxonomy IN ('category')
AND wp_trunk_terms.slug IN ('a')

SELECT wp_trunk_term_taxonomy.term_id
FROM wp_trunk_term_taxonomy
INNER JOIN wp_trunk_terms USING (term_id)
WHERE taxonomy IN ('category')
AND wp_trunk_terms.slug IN ('cat')

SELECT t.*, tt.* FROM wp_trunk_terms AS t INNER JOIN wp_trunk_term_taxonomy AS tt ON t.term_id = tt.term_id WHERE tt.taxonomy = 'category' AND t.slug = 'a' LIMIT 1

SELECT SQL_CALC_FOUND_ROWS wp_trunk_posts.* FROM wp_trunk_posts WHERE 1=1 AND wp_trunk_posts.post_type IN ('post', 'page', 'attachment') AND (wp_trunk_posts.post_status = 'publish' OR wp_trunk_posts.post_status = 'private') GROUP BY wp_trunk_posts.ID ORDER BY wp_trunk_posts.post_date DESC LIMIT 0, 1

The final query simply returns the latest post, regardless of category.

[Otto42]

Improved example of using relationship parameter in array

[Otto42]

Replying to ryan:

WP_Query( array( 'category_name' => 'A Cat', 'showposts' => '1' ) );

The category_name is supposed to be a slug, isn't it? Slugs can't have spaces in them, so it's probably splitting on the space somewhere.

[ryan]

Replying to chrisscott:

[15649] breaks existing behavior by setting post_type to 'any' instead of defaulting to 'post' when a taxonomy is specified but post_type is not. We're already being bitten by this on VIP sites on .com.

It looks like 3.0 searched "any" for custom taxonomies but not for categories and tags. Whereas 3.1 searches any for all taxonomies.

[ryan]

Replying to Otto42:

Replying to ryan:

WP_Query( array( 'category_name' => 'A Cat', 'showposts' => '1' ) );

The category_name is supposed to be a slug, isn't it? Slugs can't have spaces in them, so it's probably splitting on the space somewhere.

Before it would sanitize_title() so could get away with putting in a name. Not the best thing to due but this is a back compat break.

[ryan]

Plus, it reveals some bad behavior such as fetching a ttid that is never used and constructing a query that just returns the latest post.

[ryan]

categoryin didn't include children before. Now it does.

[ryan]

Replying to ryan:

categoryin didn't include children before. Now it does.

Hmm, it looks like it was intended to get children before. But some queries that are using categoryin in 3.0 are behaving differently than in 3.1. Still investigating.

[ryan]

How about a "no children" flag?

[ryan]

'tax_query' => array( array( 'taxonomy' => 'category', 'terms' => array(1),
					'operator' => 'IN', 'field' => 'term_id', 'include_children' => false ) ),

A shortcut for excluding children from a simple single taxonomy query would be nice. And maybe if the tax_query has only one query in it we can forego requiring the outer array().

[nickmomrik]

[16413] causes non existent categories to be treated as a category. To reproduce, visit domain.com/category/category-exists/not-exists.xml and a redirect to domain.com/category/category-exists/some-file.xml/ takes place, returns the default posts, and is_404() is false.

[scribu]

Replying to ryan:

How about a "no children" flag?

There already is one: 'include_children'. See the inline docs on get_tax_sql().

[scribu]

Replying to ryan:

And maybe if the tax_query has only one query in it we can forego requiring the outer array().

For simple queries, there still is the 'tax' => 'tag_1,tag_2' syntax.

[ryan]

Replying to scribu:

Replying to ryan:

How about a "no children" flag?

There already is one: 'include_children'. See the inline docs on get_tax_sql().

Yeah. See what I ended up using in the snippet above. Don't include children is one of the biggest requests so far. I think the child inclusion in 3.0 was a bit broken.

[ryan]

The biggest back compat issues right now are category_name not accepting full names, the post_type 'any' being used by default for category and post_tag queries, and the non-existant cat handing Nick mentions above.

[scribu]

(In [16505]) Don't set post_type to 'any' for category and tag queries. See #12891

[scribu]

Are you sure 'category_name' was supposed to match full names? get_category_by_path() expects category slugs.

[scribu]

(In [16506]) Remove debug cruft from [16505]. See #12891

[ryan]

$q['category_name'] = implode('/', array_map('sanitize_title', explode('/', $q['category_name'])));

The sanitize_title() allowed full names to be passed.The intention was for only slugs to be passed, but the code allowed full names to work.

[ryan]

(In [16511]) If the queried term does not exist make sure no posts are returned in the query rather than falling through to querying all posts. Fixes 404s when querying cats that do not exist. see #12891

[ryan]

That fix needs more thought, but it addresses the immediate concern of requests for non-existent cats returning posts instead of 404ing.

[scribu]

(In [16512]) Fix logic for when excluding a non-existant term. See #12891

[ryan]

(In [16513]) array_unique() categoryin and categorynot_in to eliminate dupes from multiple runs of parse_tax_query(). Fixes canonical redirects for cat, categoryin, and categorynot_in requests. see #12891 #15487

[scribu]

(In [16526]) Move s/ /+ out of WP_Query, so that 'category_name=Some Category' works again. See #12891

[scribu]

The logic behind [16526] is that WP_Query should not care about URL decoding.

Thus,

if you go to an URL like /category/foo+bar/ it will be interpreted as foo AND bar

but if you call get_posts('category_name=Foo Bar'), it will look for foo-bar

[scribu]

(In [16531]) Consider only IN tax queries when returning the queried object. See #12891

[demetris]

Without pretty permalinks, doing a multi-taxonomy query for cat+tag gives me a notice for undefined index “hierarchical” in line 1506 of query.php. I attach a tentative patch.

[Otto42]

After talking to scribu and nacin, here's a patch to allow a global OR in the tax_query parameter. It works like this:

query_posts(array(
	'tax_query' => array(
		'relation' => 'OR',
		array(
			'taxonomy' => 'post_format',
			'terms' => array('post-format-gallery'),
			'field' => 'slug',
		),
		array(
			'taxonomy' => 'category',
			'terms' => array('gallery'),
			'field' => 'slug',
		),
	),
) );

The relation=>OR changes the tax_query to use OR's instead of AND's on the relevant WHERE clauses. So the above query will show any post with category of gallery or a post format of gallery.

Patch is relation-or.diff, against current trunk.

[Otto42]

Add global AND/OR/XOR relationship to the tax_query setting

[scribu]

(In [16538]) Prevent notice when checking for hierarchical tax rewrite. Props demetris. See #12891

[Otto42]

Handle global AND/OR/XOR with both IN and NOT IN

[scribu]

Handle global AND/OR relations + logic fixes when empty terms

[scribu]

Due to ambiguous condition with empty terms, we decided to not support the XOR operator.

​relation-or.3.diff reflects this.

[ryan]

WP_Query('posts_per_page=15&cat=-21072,-37613,-4533907');

Produced:

SELECT SQL_CALC_FOUND_ROWS wp_trunk_posts.* FROM wp_trunk_posts WHERE 1=1 AND ( ) AND wp_trunk_posts.post_type = 'post' AND (wp_trunk_posts.post_status = 'publish' OR wp_trunk_posts.post_status = 'private') GROUP BY wp_trunk_posts.ID ORDER BY wp_trunk_posts.post_date DESC LIMIT 0, 15

None of those exclude categories exist.

[scribu]

Should be fixed in ​relation-or.4.diff.

[ryan]

I tested for regressions but ignored the new relation stuff. Canonical redirects, 404s, includes and excludes all seem good. Queries look sane. Let's commit and see how it handles in beta 1.

[scribu]

(In [16555]) Introduce 'relation' operator between tax queries. Props Otto42 for initial patch. See #12891

[maorb]

Will this new blessed feature allow us also to query multiple custom fields that are assigned to a post or a CPT?
Such as - meta_key=key1&meta_value=ValueOfKey1&meta_key=key2&meta_value=valueOfKey2

Thanks

[aaroncampbell]

Replying to maorb:
No, but #14645 did that

[ryan]

Report bugs on new tickets. Resolving this as fixed.

[mikeschinkel]

This support "x AND y" and "x OR y" but not "x AND (y OR z)" and not "x AND y AND NOT z" right?

(I know this is closed and I'm not asking to reopen, just wanted to get clarity.)

[Otto42]

Replying to mikeschinkel:

This support "x AND y" and "x OR y" but not "x AND (y OR z)" and not "x AND y AND NOT z" right?

Actually it can support all of the above, with caveats.

X AND Y and X OR Y is obviously possible for all cases.

X AND Y AND NOT Z is possible, as you can set the operator parameter to NOT IN for a single taxonomy.

X AND (Y OR Z) is possible for the special case of Y and Z being both terms in the same taxonomy. The "terms" parameter uses an operator of "IN" by default, so it would really be like "cat=X and tag IN (Y,Z)", which is basically equivalent.

See ​http://otto42.com/81 for detailed usage info, and some examples.

[mikeschinkel]

Replying to Otto42:

Replying to mikeschinkel:

This support "x AND y" and "x OR y" but not "x AND (y OR z)" and not "x AND y AND NOT z" right?

Actually it can support all of the above, with caveats.
...

See ​http://otto42.com/81 for detailed usage info, and some examples.

Thanks. I actually read that link first and wasn't clear on the above.

So I'll turn the question around; are there types of logic that it cannot handle?

[Otto42]

Replying to mikeschinkel:

Thanks. I actually read that link first and wasn't clear on the above.

So I'll turn the question around; are there types of logic that it cannot handle?

Yes. It can't do arbitrary combinations of AND's and OR's across multiple taxonomies.
For example, there is no way to specify cat=A AND (tag=B OR format=C).

"Groupings" of terms are always either "IN" or "NOT IN" and must be single taxonomies. The AND/OR is global across those groupings. This is flexible enough for almost any conceivable real-life case.

And it's not really as limiting as it seems, when you consider that A AND (B OR C) can often be rewritten as (A AND B) OR (A AND C). The idea is to get the bits in your groupings to all be of the same taxonomy, and the connecting pieces to those groupings to all be of the same type (AND or OR).

[scribu]

Follow-up: #15752

[hakre]

Related: #16464

[scribu]

Another follow-up: #16706