Ticket #5433 (new enhancement)

Opened 1 year ago

Last modified 1 month ago

Cannot combine category and tag queries in some cases

Reported by: philhassey Assigned to: ryan
Priority: high Milestone: 2.8
Component: General Version: 2.6
Severity: normal Keywords: has-patch tested
Cc: jwhavican@hotmail.com

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

5433.diff (4.0 kB) - added by Otto42 on 02/13/08 16:11:50.
Fix - First attempt
5433v2.diff (3.4 kB) - added by turboguy on 08/06/08 03:36:00.
Does not use distinct

Change History

01/24/08 18:25:36 changed by Otto42

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

02/13/08 16:07:44 changed by Otto42

  • keywords set to has-patch needs-testing 2nd-opinion.
  • 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.

02/13/08 16:11:50 changed by Otto42

  • attachment 5433.diff added.

Fix - First attempt

02/14/08 05:10:47 changed by fitztrev

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

02/14/08 20:17:10 changed by lloydbudd

  • milestone changed from 2.6 to 2.5.

02/14/08 20:31:47 changed by ryan

  • owner changed from anonymous to ryan.

02/18/08 17:40:45 changed by ryan

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

02/19/08 14:31:57 changed by 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...

02/19/08 17:35:25 changed by 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.

02/19/08 19:05:20 changed by 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

03/07/08 14:53:26 changed by westi

  • 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

03/22/08 17:42:00 changed by 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.

08/05/08 14:37:32 changed by turboguy

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

08/05/08 21:37:18 changed by turboguy

  • keywords changed from has-patch needs-testing 2nd-opinion to has-patch needs-testing.

08/05/08 21:42:12 changed by ryan

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

08/06/08 03:36:00 changed by turboguy

  • attachment 5433v2.diff added.

Does not use distinct

08/06/08 03:38:36 changed by 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.

08/07/08 14:08:53 changed by turboguy

  • version changed from 2.5 to 2.6.

08/11/08 20:46:43 changed by turboguy

  • keywords changed from has-patch needs-testing to has-patch tested.

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?

08/11/08 20:50:54 changed by ryan

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

09/03/08 16:26:47 changed by turboguy

  • cc set to jwhavican@hotmail.com.

10/16/08 23:13:56 changed by ryan

  • type changed from defect to enhancement.
  • milestone changed from 2.7 to 2.8.

10/19/08 02:35:34 changed by turboguy

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