WordPress.org

Make WordPress Core

Opened 8 years ago

Closed 6 years ago

#5433 closed enhancement (fixed)

Cannot combine category and tag queries in some cases

Reported by: philhassey Owned by: ryan
Milestone: 2.8 Priority: normal
Severity: normal Version: 2.6
Component: Taxonomy Keywords: has-patch tested
Focuses: Cc:

Description

I am trying to find all posts that are in the intersection of category x and tag a. I would expect that this query to my blog would work:

/?category_name=x&tag=a : Gives a 404 -- even though I have several items in category x && tag a.

However, I have found that querying a single category with multiple tags does work:

/?category_name=x&tag=a+b : Gives results of items in category x && tag a && tag b.

As a work-a-round for my issue, I've found that this works:

/?category_name=x&tag=a+a : Gives results of items in category x && tag a

I found this work-a-round by searching through includes/query.php to see if I could troubleshoot the issue. I'm not quite sure how to fix the issue, but it appears that
tag=a uses a different query than tag=a+a ..

I hope my explanation was easy to follow, but if not, I'd be glad to try to explain further, or provide an example on my blog.

Attachments (2)

5433.diff (4.0 KB) - added by Otto42 7 years ago.
Fix - First attempt
5433v2.diff (3.4 KB) - added by turboguy 7 years ago.
Does not use distinct

Download all attachments as: .zip

Change History (36)

comment:1 @Otto427 years ago

  • Priority changed from normal to high
  • Summary changed from URL Queries /?category_name=x&tag=a don't work to Cannot combine category and tag queries in some cases

The underlying cause of the problem is how the queries are built.

The "cat" and "category_name" are mapped to be equivalent to the internal "categoryin" (or "category_notin" for negative values of "cat").

Similarly, "tag" is mapped to be equivalent to the internal "tag_slugin" when you use commas, and "tag_slugand" when you use plus signs.

Categoryin causes this inner join:
INNER JOIN $wpdb->term_relationships ON ($wpdb->posts.ID = $wpdb->term_relationships.object_id) INNER JOIN $wpdb->term_taxonomy ON ($wpdb->term_relationships.term_taxonomy_id = $wpdb->term_taxonomy.term_taxonomy_id)

And this to be added to the WHERE clause:
AND $wpdb->term_taxonomy.taxonomy = 'category'

Similarly, tag_slugin (and tagin) causes the exact same inner join, with this added to the WHERE:
AND $wpdb->term_taxonomy.taxonomy = 'post_tag'

These two are mutually exclusive, no post can satisfy them. Thus the 404 error you get. The fix for this is to move the check for 'category' and 'post_tag' taxonomies into the second inner join, like so:
INNER JOIN $wpdb->term_relationships ON ($wpdb->posts.ID = $wpdb->term_relationships.object_id) INNER JOIN $wpdb->term_taxonomy ON ($wpdb->term_relationships.term_taxonomy_id = $wpdb->term_taxonomy.term_taxonomy_id AND $wpdb->term_taxonomy.taxonomy = 'category')

Same for the tags. This causes the join to only join the category and/or post tags correctly. There may need to be some "AS tag" and "AS category" bits added to make the tags/categories more specific here, in case both are included.

Regarding the multi-tag queries, these use a separate query to get a list of post-id's which is then checked by this:
AND $wpdb->posts.ID IN (list of posts)

Which is why they combine so readily.

comment:2 @Otto427 years ago

  • Keywords has-patch needs-testing 2nd-opinion added
  • Version changed from 2.3.1 to 2.5

Attached rough draft of a patch for trunk. Have not tested yet, would like somebody else to see how it works as well.

@Otto427 years ago

Fix - First attempt

comment:3 @fitztrev7 years ago

As per the discussion on the hackers list, I'd like to request the milestone for this ticket be changed to 2.5.

comment:4 @lloydbudd7 years ago

  • Milestone changed from 2.6 to 2.5

comment:5 @ryan7 years ago

  • Owner changed from anonymous to ryan

comment:6 @ryan7 years ago

If we can get some testing on this, we can try to get it in. Otherwise punting to 2.6.

comment:7 @BjornW7 years ago

I tried this patch with Wordpress 2.3.1 on MySQL 5.0.27 with PHP5.2.4 on my local machine. It didn't work and I get:

WordPress database error: [Not unique table/alias: 'wp_term_relationships']

I'll have a look if I can fix this with a new alias...

comment:8 @BjornW7 years ago

I had to add aliases so that the innerjoins would work. This seems to work for me on Wordpress 2.3.1 I hope to add a diff when I get some time to also test this on trunk.

comment:9 @fitztrev7 years ago

I tried Otto's patch (5433.diff) on trunk, 6913, but can't get the desired behavior.

I gave Hello World a tag of "testing", then tried accessing /?category_name=uncategorized&tag=testing, but it resulted in a 404, the same as with no patch applied. Same with /?cat=1&tag=testing

comment:10 @westi7 years ago

  • Milestone changed from 2.5 to 2.6

Punting to 2.6 as the testing so far does not give enough positive results for it's inclusion in 2.5

comment:11 @Otto427 years ago

This patch should have worked, but MySQL can be a bit more finicky than I was expecting. I'll take Bjorn's advice and add aliases to see if I can make it work for more cases. Gimme a few days.

comment:12 @turboguy7 years ago

  • Milestone changed from 2.9 to 2.6.1

I've created a patch for this bug. I've tested it using the following:
`
?cat=1&tag=tag1
?cat=1&tag=tag1+tag2
?cat=1,2&tag=tag1+tag2
?cat=1&tag=tag1,tag2
`
It seems to be working so far. Really this whole function should be rewritten but this should work until that happens. Please test this as soon as you can so we can get it into the next release.

comment:13 @turboguy7 years ago

  • Keywords 2nd-opinion removed

comment:14 @ryan7 years ago

  • Milestone changed from 2.6.1 to 2.7

I haven't looked too closely yet, but DISTINCT is a red flag. That can kill query performance. It should be used only in the cases that require it.

@turboguy7 years ago

Does not use distinct

comment:15 @turboguy7 years ago

Okay, I added a new version of 5433v2.diff. This one doesn't use DISTINCT, I didn't realize that it had such an impact on performance. I works just the same without it. Hope you can take a look at it soon.

comment:16 @turboguy7 years ago

  • Version changed from 2.5 to 2.6

comment:17 @turboguy7 years ago

  • Keywords tested added; needs-testing removed

I've been using this patch (5433v2.diff) for about a week now and it's been working perfectly. I've been using it on WordPress MU 2.6. Is there any chance of getting it into 2.6.1?

comment:18 @ryan7 years ago

2.6.1 is releasing soon and this change is a bit too big, sorry.

comment:19 @turboguy7 years ago

  • Cc jwhavican@… added

comment:20 @ryan7 years ago

  • Milestone changed from 2.7 to 2.8
  • Type changed from defect to enhancement

comment:21 @turboguy7 years ago

Is there anything I can do to help get this into 2.7? It seems like it's ready to go.

comment:22 @mrmist6 years ago

  • Keywords dev-feedback added

comment:23 @ryan6 years ago

  • Component changed from General to Taxonomy

comment:24 @Denis-de-Bernardy6 years ago

patch still applies cleanly against trunk

comment:25 @josswinn6 years ago

  • Cc josswinn added

I'm trying this on WP 2.7.1 and WPMU 2.7.1 with pretty URLs and can't get the suggested queries to work.

The patch has been applied cleanly on this install:

http://learninglab.lincoln.ac.uk/wptest/

Can anyone tell me what a working URL would be to combine the category of 'world' with 'tag4' and 'tag2' ?

http://learninglab.lincoln.ac.uk/wptest/?cat=world&tag=tag4+tag2 doesn't work.

Also, when this does work, can you also produce a feed from the query? :-)

Thanks.

comment:26 follow-up: @DD326 years ago

http://learninglab.lincoln.ac.uk/wptest/?cat=world&tag=tag4+tag2 doesn't work.

Not surprised, Because you have nothing that matches that.

http://learninglab.lincoln.ac.uk/wptest/?category_name=world&tag=tag2,tag4 works.

cat accepts Cat ID's only,not names.

tag2+tag4 means something tagged as BOTH tag2 and tag4. tag2,tag4 means tagged EITHER tag2 OR tag4

comment:28 in reply to: ↑ 26 @josswinn6 years ago

Replying to DD32:

http://learninglab.lincoln.ac.uk/wptest/?cat=world&tag=tag4+tag2 doesn't work.

Not surprised, Because you have nothing that matches that.

http://learninglab.lincoln.ac.uk/wptest/?category_name=world&tag=tag2,tag4 works.

cat accepts Cat ID's only,not names.

tag2+tag4 means something tagged as BOTH tag2 and tag4. tag2,tag4 means tagged EITHER tag2 OR tag4

Thanks. This clears up my misunderstanding of the required URL syntax.

comment:29 @Denis-de-Bernardy6 years ago

  • Keywords dev-feedback removed

so... invalid?

comment:30 follow-up: @ryan6 years ago

  • Milestone 2.8 deleted
  • Resolution set to invalid
  • Status changed from new to closed

Seems like. If I've misunderstood the thread, reopen.

comment:31 in reply to: ↑ 30 @josswinn6 years ago

  • Milestone set to 2.8
  • Resolution invalid deleted
  • Status changed from closed to reopened

Replying to ryan:

Seems like. If I've misunderstood the thread, reopen.

Not sure what you mean by 'invalid' but this patch works and it would be good to get it included as soon as possible. My comment above was the result of my own misunderstanding about the URL syntax required. Nothing wrong with the patch.

comment:32 @ryan6 years ago

  • Priority changed from high to normal

comment:33 @turboguy6 years ago

I'm glad to see this is working for everyone. I really hope we can get it committed for 2.8.

comment:34 @automattor6 years ago

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

(In [11348]) Allow combining category and tag queries. Props turboguy. fixes #5433

Note: See TracTickets for help on using tickets.