WordPress.org

Make WordPress Core

Opened 4 years ago

Closed 3 years ago

Last modified 3 years ago

#12891 closed task (blessed) (fixed)

Advanced multi-taxonomy WP_Query()s

Reported by: markjaquith Owned by: markjaquith
Milestone: 3.1 Priority: normal
Severity: normal Version: 3.0.1
Component: Query Keywords: ongoing-project
Focuses: Cc:

Description

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.

Attachments (28)

lazy.php (1.8 KB) - added by scribu 4 years ago.
delayed parsing
eager.php (1.6 KB) - added by scribu 4 years ago.
generate SQL string directly
eager.2.php (1.7 KB) - added by scribu 4 years ago.
corrected SQL
12891.diff (7.1 KB) - added by scribu 4 years ago.
Start to pull tax SQL out of WP_Query and into _wp_tax_sql()
12891.2.diff (17.0 KB) - added by scribu 4 years ago.
Multiple taxonomy queries supported
12891.3.diff (19.2 KB) - added by scribu 4 years ago.
Introduce wp_tax_query() and use it in get_objects_in_term()
12891.4.diff (19.8 KB) - added by scribu 4 years ago.
Extend get_objects_in_term() and use it in wp_tax_query() instead
12891-cat-query-var.patch (1.1 KB) - added by Utkarsh 3 years ago.
12891-better_get_tax_sql_error_handling.patch (448 bytes) - added by Viper007Bond 3 years ago.
revert-tax.diff (18.9 KB) - added by ryan 3 years ago.
query.php.r15471.diff (42.4 KB) - added by nacin 3 years ago.
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).
query.php.r15471.2.diff (44.3 KB) - added by nacin 3 years ago.
scale.12891.diff (6.7 KB) - added by scribu 3 years ago.
scale.12891.2.diff (7.0 KB) - added by scribu 3 years ago.
Optimized subqueries
query.php.r15471.2.2.diff (44.3 KB) - added by nacin 3 years ago.
scale.12891.3.diff (7.3 KB) - added by scribu 3 years ago.
Use term_taxonomy_id directly
scale.12891.4.diff (8.7 KB) - added by scribu 3 years ago.
Use JOINs
scale.12891.4.2.diff (8.7 KB) - added by scribu 3 years ago.
Use JOINs + avoid extra query from 'term_id' to 'term_id'
scale.12891.5.diff (9.0 KB) - added by scribu 3 years ago.
Use JOIN for IN and subquery for NOT IN
taxonomy.diff (1.0 KB) - added by tott 3 years ago.
Prepend table names to resulting field params for _transform_terms
scale.12891.6.diff (862 bytes) - added by scribu 3 years ago.
Put NOT IN outside of subquery
scale.12891.6b.diff (950 bytes) - added by markjaquith 3 years ago.
Updated patch (old one had conflicts).
get-tax-sql-or.diff (2.2 KB) - added by Otto42 3 years ago.
Improved example of using relationship parameter in array
t21891-isset-taxonomy-rewrite-hierarchical.diff (437 bytes) - added by demetris 3 years ago.
relation-or.diff (1.1 KB) - added by Otto42 3 years ago.
Add global AND/OR/XOR relationship to the tax_query setting
relation-or.2.diff (1.3 KB) - added by Otto42 3 years ago.
Handle global AND/OR/XOR with both IN and NOT IN
relation-or.3.diff (1.8 KB) - added by scribu 3 years ago.
Handle global AND/OR relations + logic fixes when empty terms
relation-or.4.diff (1.8 KB) - added by scribu 3 years ago.

Download all attachments as: .zip

Change History (212)

comment:1 markjaquith4 years ago

Related (less ambitious): #9951

comment:2 demetris4 years ago

  • Cc dkikizas@… added

comment:3 ptahdunbar4 years ago

  • Cc trac@… added

comment:4 scribu4 years ago

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)

comment:5 follow-up: markjaquith4 years ago

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.

comment:6 willmot4 years ago

  • Cc willmot added

comment:7 in reply to: ↑ 5 scribu4 years ago

  • Keywords WP_Query removed

Replying to markjaquith:

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

Anyway, I like the potential of this proposed API.

scribu4 years ago

delayed parsing

comment:8 scribu4 years ago

  • Keywords has-patch added

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.

scribu4 years ago

generate SQL string directly

scribu4 years ago

corrected SQL

comment:9 scribu4 years ago

Related: #7463

comment:10 johnbillion4 years ago

  • Cc johnbillion@… added

comment:11 beaulebens4 years ago

  • Cc beau@… added

comment:12 follow-up: filosofo4 years ago

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.

comment:13 markjaquith4 years ago

  • Milestone changed from Awaiting Triage to 3.1
  • Owner changed from ryan to markjaquith
  • Status changed from new to assigned
  • Type changed from feature request to task (blessed)
  • Version changed from 3.0 to 3.0.1

I will bless and take responsibility for this one.

comment:14 aaroncampbell4 years ago

  • Cc aaroncampbell added

comment:15 in reply to: ↑ 12 mikeschinkel4 years ago

  • Cc mikeschinkel@… added

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.

comment:16 scribu4 years ago

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.

comment:17 follow-up: scribu4 years ago

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'
  )
) );

comment:18 in reply to: ↑ 17 mikeschinkel4 years ago

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)

?

comment:19 follow-up: scribu4 years ago

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.

comment:20 in reply to: ↑ 19 mikeschinkel4 years ago

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.

comment:21 scribu4 years ago

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.

comment:22 scribu4 years ago

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.

comment:23 mikeschinkel4 years ago

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

comment:24 scribu4 years ago

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

scribu4 years ago

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

comment:25 follow-up: markjaquith4 years ago

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.

comment:26 in reply to: ↑ 25 mikeschinkel4 years ago

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? ;-)

comment:27 scribu4 years ago

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.

comment:28 scribu4 years ago

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

comment:29 scribu4 years ago

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

scribu4 years ago

Multiple taxonomy queries supported

scribu4 years ago

Introduce wp_tax_query() and use it in get_objects_in_term()

comment:30 scribu4 years ago

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

scribu4 years ago

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

comment:31 scribu4 years ago

Related: #14831

comment:32 peterjanes4 years ago

  • Cc trac.wordpress.org@… added

comment:33 scribu4 years ago

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

comment:34 scribu4 years ago

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.

comment:35 mikeschinkel4 years ago

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.)

comment:36 automattor4 years ago

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

comment:37 follow-up: scribu4 years ago

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

comment:38 scribu4 years ago

Related: #12659

comment:39 in reply to: ↑ 37 mikeschinkel4 years ago

Replying to scribu:

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

#14869

comment:40 automattor4 years ago

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

comment:41 scribu4 years ago

Marked #14699 as dup.

comment:42 scribu4 years ago

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

comment:43 follow-up: dd324 years ago

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.

comment:44 dd324 years ago

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

comment:45 in reply to: ↑ 43 scribu4 years ago

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().

comment:46 scribu4 years ago

Or at least it was. Looking into it now.

comment:47 dd324 years ago

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.

comment:48 scribu4 years ago

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

comment:49 lumpysimon4 years ago

  • Cc piemanek@… added

comment:50 scribu4 years ago

Related: #15007

comment:51 scribu4 years ago

Related: #15032

comment:52 scribu4 years ago

(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.

comment:53 scribu4 years ago

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

comment:54 dd324 years ago

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/)

comment:55 scribu4 years ago

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

comment:56 markjaquith4 years ago

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?

comment:57 scribu4 years ago

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

comment:58 scribu4 years ago

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

comment:59 follow-up: scribu4 years ago

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'
    ),
  )
) );

comment:60 scribu4 years ago

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

comment:61 in reply to: ↑ 59 mikeschinkel4 years ago

Replying to scribu:

Done. Advanced query example updated:

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

comment:62 scribu4 years ago

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

comment:63 scribu4 years ago

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

comment:64 scribu4 years ago

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

comment:65 hakre4 years ago

Last patch might have permalink compability implications.

comment:66 scribu4 years ago

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

comment:67 Utkarsh4 years ago

  • Cc admin@… added

comment:68 scribu3 years ago

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

comment:69 automattor3 years ago

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

comment:70 scribu3 years ago

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

comment:71 Utkarsh3 years ago

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

comment:72 scribu3 years ago

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

comment:73 ramenboy3 years ago

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"

comment:74 scribu3 years ago

Turns out it was a problem with the plugin.

comment:76 nacin3 years ago

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.

comment:77 scribu3 years ago

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

comment:78 scribu3 years ago

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?

comment:79 scribu3 years ago

Related: #15363

comment:80 scribu3 years ago

  • Keywords ongoing-project added; taxonomies has-patch removed

Also related: #14880 #15161

comment:81 dd323 years ago

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).

comment:82 dd323 years ago

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

comment:83 Viper007Bond3 years ago

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...

comment:84 scribu3 years ago

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

comment:85 ryan3 years ago

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.

comment:86 ryan3 years ago

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

Ouch!

comment:87 ryan3 years ago

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

comment:88 ryan3 years ago

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

comment:89 ryan3 years ago

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.

comment:90 scribu3 years ago

I thought I fixed such cases with [16380].

comment:91 ryan3 years ago

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

comment:92 ryan3 years ago

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.

comment:93 ryan3 years ago

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.

comment:94 ryan3 years ago

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)

comment:95 follow-up: ryan3 years ago

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.

comment:96 ryan3 years ago

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.

ryan3 years ago

nacin3 years ago

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).

comment:97 scribu3 years ago

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

comment:98 follow-up: scribu3 years ago

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

comment:99 in reply to: ↑ 98 nacin3 years ago

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.

comment:100 scribu3 years ago

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

comment:101 ryan3 years ago

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

comment:102 scribu3 years ago

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

comment:103 scribu3 years ago

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

nacin3 years ago

comment:104 nacin3 years ago

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:

scribu3 years ago

comment:105 scribu3 years ago

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.

comment:106 scribu3 years ago

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

comment:107 in reply to: ↑ 95 scribu3 years ago

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.

scribu3 years ago

Optimized subqueries

comment:108 scribu3 years ago

scale.12891.2.diff:

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

nacin3 years ago

comment:109 scribu3 years ago

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

comment:111 scribu3 years ago

That post isn't very thorough.

comment:112 ryan3 years ago

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?

comment:113 ryan3 years ago

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. :-)

comment:114 scribu3 years ago

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.

comment:115 ryan3 years ago

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()

comment:116 ryan3 years ago

The unbounded subqueries kill performance.

comment:117 ryan3 years ago

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

comment:118 scribu3 years ago

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

scribu3 years ago

Use term_taxonomy_id directly

scribu3 years ago

Use JOINs

comment:119 scribu3 years ago

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

comment:120 ryan3 years ago

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.

scribu3 years ago

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

scribu3 years ago

Use JOIN for IN and subquery for NOT IN

comment:121 ryan3 years ago

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

comment:122 scribu3 years ago

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

comment:123 ryan3 years ago

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

comment:124 scribu3 years ago

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

comment:125 scribu3 years ago

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

comment:126 tott3 years ago

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.

tott3 years ago

Prepend table names to resulting field params for _transform_terms

comment:127 scribu3 years ago

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

comment:128 follow-up: ryan3 years ago

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.

comment:129 in reply to: ↑ 128 foofy3 years ago

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)
)";

scribu3 years ago

Put NOT IN outside of subquery

comment:130 scribu3 years ago

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

markjaquith3 years ago

Updated patch (old one had conflicts).

comment:131 ryan3 years ago

Seems to perform about the same.

comment:132 markjaquith3 years ago

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

comment:133 ryan3 years ago

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.

comment:134 ryan3 years ago

We might have to bring that extra join back.

comment:135 ryan3 years ago

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

comment:136 scribu3 years ago

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

comment:137 ryan3 years ago

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. :-)

comment:138 chrisscott3 years ago

[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.

comment:139 Otto423 years ago

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?

comment:140 Otto423 years ago

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.

comment:141 Otto423 years ago

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.

comment:142 follow-up: ryan3 years ago

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.

Otto423 years ago

Improved example of using relationship parameter in array

comment:143 in reply to: ↑ 142 ; follow-up: Otto423 years ago

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.

comment:144 ryan3 years ago

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.

comment:145 in reply to: ↑ 143 ryan3 years ago

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.

comment:146 ryan3 years ago

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.

comment:147 follow-up: ryan3 years ago

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

comment:148 in reply to: ↑ 147 ryan3 years ago

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.

comment:149 follow-up: ryan3 years ago

How about a "no children" flag?

comment:150 follow-up: ryan3 years ago

'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().

comment:151 nickmomrik3 years ago

[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.

comment:152 in reply to: ↑ 149 ; follow-up: scribu3 years ago

Replying to ryan:

How about a "no children" flag?

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

comment:153 in reply to: ↑ 150 scribu3 years ago

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.

comment:154 in reply to: ↑ 152 ryan3 years ago

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.

comment:155 ryan3 years ago

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.

comment:156 scribu3 years ago

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

comment:157 scribu3 years ago

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

comment:158 scribu3 years ago

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

comment:159 ryan3 years ago

$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.

comment:160 ryan3 years ago

(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

comment:161 ryan3 years ago

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

comment:162 scribu3 years ago

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

comment:163 ryan3 years ago

(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

comment:164 scribu3 years ago

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

comment:165 scribu3 years ago

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

comment:166 scribu3 years ago

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

comment:167 demetris3 years ago

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.

comment:168 Otto423 years ago

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.

Otto423 years ago

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

comment:169 scribu3 years ago

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

Otto423 years ago

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

scribu3 years ago

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

comment:170 scribu3 years ago

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

relation-or.3.diff reflects this.

comment:171 ryan3 years ago

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.

scribu3 years ago

comment:172 scribu3 years ago

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

comment:173 ryan3 years ago

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.

comment:174 scribu3 years ago

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

comment:175 follow-up: maorb3 years ago

  • Cc maorb added

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

comment:176 in reply to: ↑ 175 aaroncampbell3 years ago

Replying to maorb:
No, but #14645 did that

comment:177 ryan3 years ago

  • Resolution set to fixed
  • Status changed from assigned to closed

Report bugs on new tickets. Resolving this as fixed.

comment:178 follow-up: mikeschinkel3 years ago

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.)

comment:179 in reply to: ↑ 178 ; follow-up: Otto423 years ago

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.

comment:180 in reply to: ↑ 179 ; follow-up: mikeschinkel3 years ago

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?

comment:181 in reply to: ↑ 180 Otto423 years ago

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).

comment:182 scribu3 years ago

Follow-up: #15752

comment:183 hakre3 years ago

Related: #16464

comment:184 scribu3 years ago

Another follow-up: #16706

Note: See TracTickets for help on using tickets.