Query Posts Tag ANY with Category ANY

Did you get this working? I still see 8 results – or am I doing something wrong?

The link above is the search with tags. If you add a category from there using the control at the top of the page the results will come back empty even if there should be results. You can tell because if you hover the mouse over the date of any result, it will show a popup with the categories and tags for that result.

Here is what I did:

• Click on CATEGORIES
• Click on Defenders
• Click on All
• Hover on one of 8 pics – shows both category and tag

Number of posts went from 12 to 8. Is that not correct?

Sorry! I meant ANY! I don’t know why I said all. The ALL controls are working. It’s when I combine tag__in and category__in (ANY) that it creates the problem.

Can you post the resulting query parameters or the actual query?

Here’s what I’m passing to query_posts():

Array ( [paged] => 1 [tag__in] => Array ( [0] => 1012 [1] => 1223 ) [posts_per_page] => 10 [post_type] => post )

And WITH the category:

Array ( [paged] => 1 [category__in] => Array ( [0] => 25 ) [tag__in] => Array ( [0] => 1012 [1] => 1223 ) [posts_per_page] => 10 [post_type] => post )

Ah sorry, you said result…

Here’s the query that was returned in $wp_query->request after the query_posts function:

SELECT SQL_CALC_FOUND_ROWS jt_posts.* FROM jt_posts INNER JOIN jt_term_relationships ON (jt_posts.ID = jt_term_relationships.object_id) INNER JOIN jt_term_taxonomy ON (jt_term_relationships.term_taxonomy_id = jt_term_taxonomy.term_taxonomy_id) WHERE 1=1 AND jt_term_taxonomy.taxonomy = ‘post_tag’ AND jt_term_taxonomy.term_id IN (‘1012’, ‘1223’) AND(post_type = ‘post’ OR post_type = ‘page’) AND (jt_posts.post_status = ‘publish’) GROUP BY jt_posts.ID ORDER BY jt_posts.post_date DESC LIMIT 0, 10

And here it is with the category:

SELECT SQL_CALC_FOUND_ROWS jt_posts.* FROM jt_posts INNER JOIN jt_term_relationships ON (jt_posts.ID = jt_term_relationships.object_id) INNER JOIN jt_term_taxonomy ON (jt_term_relationships.term_taxonomy_id = jt_term_taxonomy.term_taxonomy_id) WHERE 1=1 AND jt_term_taxonomy.taxonomy = ‘category’ AND jt_term_taxonomy.term_id IN (’25’) AND jt_term_taxonomy.taxonomy = ‘post_tag’ AND jt_term_taxonomy.term_id IN (‘1012’, ‘1223’) AND(post_type = ‘post’ OR post_type = ‘page’) AND (jt_posts.post_status = ‘publish’) GROUP BY jt_posts.ID ORDER BY jt_posts.post_date DESC LIMIT 0, 10

Running the queries on the database directly in PHPMyAdmin gives the same result so I’m sure the query is working correctly. It seems to be some kind of bug in the way the query is built in WordPress itself.

I remember reading something about tag and category combinations not working well unless you doubled the tag elements or had more than one, but I already have more than one… I don’t know what to do if anything.

OK – the problem seems to be that the query is asking for “jt_term_taxonomy.taxonomy = ‘category’ AND jt_term_taxonomy.taxonomy = ‘post_tag”. Both conditions cannot be true at the same time.

Since query_posts is building the query, I doubt that you can change that easily. Looks like a bug (feature?) in query_posts.

To make it work, you would need to join jt_term_taxonomy twice with two aliases. Then use one alias with the catgory selection and the other with the post_tag selection.

vtxyzzy, you’re a god for helping me so quickly and at this level. Thank you!

Can you tell me what’s wrong with this query then?

SELECT SQL_CALC_FOUND_ROWS jt_posts.* FROM jt_posts INNER JOIN jt_term_relationships ON (jt_posts.ID = jt_term_relationships.object_id) INNER JOIN jt_term_taxonomy as t1 ON (jt_term_relationships.term_taxonomy_id = t1.term_taxonomy_id)INNER JOIN jt_term_taxonomy as t2 ON (jt_term_relationships.term_taxonomy_id = t2.term_taxonomy_id) WHERE 1=1 AND t1.taxonomy = ‘category’ AND t1.term_id IN (’25’) AND t2.taxonomy = ‘post_tag’ AND t2.term_id IN (‘1323’, ‘1585’, ‘1012’, ’90’) AND(post_type = ‘post’ OR post_type = ‘page’) AND (jt_posts.post_status = ‘publish’) GROUP BY jt_posts.ID ORDER BY jt_posts.post_date DESC LIMIT 0, 10

I’m trying to hack the query by using string replace to put in the values that should be there (temporary fix, but I’ll settle for that for now).

Doing it the way you suggested would kind of work, but how would I handle paging? I can hide the results that aren’t in both sets, but then how do I know the total number of pages? Anyway, if I can at least modify the query to what it’s supposed to be, that will get the job done.

I can’t be absolutely sure without some testing, but I suspect that you need two instances of jt_term_relationships – one for each instance of jt_term_taxonomy.

I was thinking that too… What’s the proper way of doing that, just inner join twice with two aliases just like the other?

This is getting pretty complex, but I think this is what you want:

SELECT SQL_CALC_FOUND_ROWS jt_posts.*
FROM jt_posts
   INNER JOIN jt_term_relationships as r1
      ON (jt_posts.ID = r1.object_id)
   INNER JOIN jt_term_relationships as r2
      ON (jt_posts.ID = r2.object_id)
   INNER JOIN jt_term_taxonomy as t1
      ON (r1.term_taxonomy_id = t1.term_taxonomy_id)
   INNER JOIN jt_term_taxonomy as t2
      ON (r2.term_taxonomy_id = t2.term_taxonomy_id)
WHERE 1=1
AND t1.taxonomy = 'category'
AND t1.term_id IN ('25')
AND t2.taxonomy = 'post_tag'
AND t2.term_id IN ('1323', '1585', '1012', '90')
AND(post_type = 'post' OR post_type = 'page')
AND (jt_posts.post_status = 'publish')
GROUP BY jt_posts.ID
ORDER BY jt_posts.post_date DESC LIMIT 0, 10

If you don’t mind hacking the core, you can modify wp-includes/query.php around lines 1799 and 1842 to put in the aliases for the category part of the query.

I think once you have aliases for category, the tags code can remain the same.

And BOOM. It works :D!!!!

Thank you for your query experience. I really didn’t want to have to take the hours it would have taken me to figure out what the correct query would have been (if I could have at all!).

With the correct query, I’m able to modify the query string on the fly making it correct and functional. I have to modify the query, not the core because this is a plugin that I want others to be able to use.