I'm building an advanced search for my site, and after trying to accomplish what I want using WPs own simpler commands, realised that I was going to need to use $wpdb to dynamically create an SQL query. Most of it is working great, but I have a problem with combining multiple taxonomies.
I have two main taxonomies (it's for a restauran listing site): 'Cuisines' and 'Good for'. On the search, each one has multiple checkboxes. What I want is that if you select multiple checkboxes for cuisines (for example) then the search returns all the listings that have one or more of these cuisines (and excluding those that have none). This is pretty simple - can use the SQL 'IN' to create a line in the query like this:
AND wp_term_taxonomy.term_id IN (41, 43, 45)
Where 41,43,45 are categories of different cuisines.
This also works fine for the Good For, on its own, but when combining the two lots of categories is where I'm having the problem. Obviously putting all the category numbers into the same line will produce restaurants that either fulfil one of the cuisines OR one of the Good Fors - but what I want is all the listings which fulfil any of the possible cuisines (OR) AND any of the possible 'good fors'.
Yet having two lines in the SQL like this:
AND wp_term_taxonomy.term_id IN (41, 43, 45) AND wp_term_taxonomy.term_id IN (68, 69, 81)
throws up no results, even though I know that there might be a restaurant that has cuisine 41 and good for 68, for example.
I can't figure out another way to structure the query, can anyone help me?