Results 1 to 3 of 3
  1. #1
    Join Date
    Jan 2002
    Posts
    189

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

    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?

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Jan 2002
    Posts
    189
    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..

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •