WordPress.org

1. kinkersnick
   Member
   Posted 10 months ago #
   

   Hi,

   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?

   Thanks!

2. kinkersnick
   Member
   Posted 10 months ago #
   

   Solved this one myself:

   In case anyone is interested, this is the full query that enables me to search two sets of taxonomies, each with an internal OR condition (via 'IN') but combined via an AND condition:

   SELECT * FROM wp_posts
   LEFT JOIN wp_term_relationships AS cuisine ON (wp_posts.ID = cuisine.object_id)
   LEFT JOIN wp_term_relationships AS goodfor ON (wp_posts.ID = goodfor.object_id) WHERE wp_posts.post_type = 'listings'
   AND wp_posts.post_status = 'publish'
   AND cuisine.term_taxonomy_id IN (41, 42, 45)
   AND goodfor.term_taxonomy_id IN (55, 57)

   X

3. kinkersnick
   Member
   Posted 10 months ago #
   

   Minor update (in case anyone is interested):

   Need to add:

   GROUP BY $wpdb->posts.ID

   to the end of the query to avoid duplicate posts.

4. jnhghy
   Member
   Posted 10 months ago #
   

   Nice to see someone fixing his/hers issue like this .... nice going!

5. mcdonna
   Member
   Posted 6 months ago #
   

   agree ... kudos, @kinkersnick, for figuring this out and sharing it with others facing a similar problem!

Reply

You must log in to post.