Cannot combine category and tag queries in some cases

[philhassey]

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.

[Otto42]

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.

[Otto42]

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

[Otto42]

Fix - First attempt

[fitztrev]

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

[ryan]

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

[BjornW]

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

[BjornW]

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.

[fitztrev]

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

[westi]

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

[Otto42]

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.

[turboguy]

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.

[ryan]

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.

[turboguy]

Does not use distinct

[turboguy]

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.

[turboguy]

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?

[ryan]

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

[turboguy]

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

[Denis-de-Bernardy]

patch still applies cleanly against trunk

[josswinn]

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.

[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

[DD32]

and for feeds, just add feed=rss2 to it:

​http://learninglab.lincoln.ac.uk/wptest/?category_name=world&tag=tag2,tag3&feed=rss2

[josswinn]

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.

[Denis-de-Bernardy]

so... invalid?

[ryan]

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

[josswinn]

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.

[turboguy]

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

[automattor]

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