Multiple IN() statements - is there a better way to group optional search IDs?

Spudhead · #1 (**permalink**) 01-17-11, 08:16

I'm trying to build a query for pulling out wordpress posts based on multiple categories. The catgories are grouped into parent categories, ie:

Animals
- Fish
- Dog
- Cat

OS's
- Windows
- OSX
- Ubuntu

A user could select "Fish" and "Dog", and "OSX" (although God knows what they'd be looking for...) - the query would match anything that was tagged OSX, and either "Fish" OR "Dog"

The way I have it is multiple IN() statements, ie:

Code:

WHERE term_taxonomy.taxonomy = 'category'
AND (
	term_taxonomy.term_id IN(1,2)
	AND term_taxonomy.term_id IN(3,4)
)

However, there may be several groups of categories that I need to search for, and I'm a bit concerned that this isn't a very efficient way of doing it. Is there a better way?

r937 · #2 (**permalink**) 01-17-11, 08:21

change this --

Code:

WHERE term_taxonomy.taxonomy = 'category'
AND (
	term_taxonomy.term_id IN(1,2)
	AND term_taxonomy.term_id IN(3,4)
)

to this --

Code:

WHERE term_taxonomy.taxonomy = 'category'
AND term_taxonomy.term_id IN(1,2,3,4)

although the performance improvement will be rather minuscule

Spudhead · #3 (**permalink**) 01-17-11, 09:36

Sorry, I wasn't very clear. I need those groups of category ID's to be mandatory; it should match posts that have at least one of the grouped category ID's.

So IN(1,2,3,4) would match a post in any of those categories. I want it to match a post that's in either of categories 1 or 2, AND either of categories 3 or 4.

My concern about performance is that I might be chaining lots of those statements together, like:

term_taxonomy.term_id IN([list of categories])
AND term_taxonomy.term_id IN([another list of categories])
AND term_taxonomy.term_id IN([yet another list of categories])
etc, etc

If that's not actually going to be much of a performance issue - or not one I can do much about - then it's all academic anyway..

Internet Services

Web Development

Digital Marketing

Consumer Tech