I'm trying to create an advanced search plugin that will allow users to select which categories results should be from. Categories can be grouped to form AND and OR parts of the query.
Group 1 Group 2 [X] Category_1 [ ] Category_4 [X] Category_2 [X] Category_5 [ ] Category_3
Assuming category 1, 2 and 5 are checked, query should return posts that are IN (Category_1 OR Category_2) AND IN (Category_5)
Using posts_join, posts_where and posts_groupby I have been able to code a full search engine except for the last part.
The simplified query becomes:
SELECT * FROM wp_posts INNER JOIN wp_term_relationships ON wp_posts.ID = wp_term_relationships.object_id INNER JOIN wp_term_taxonomy ON wp_term_relationships.term_taxonomy_id = wp_term_taxonomy.term_taxonomy_id WHERE wp_posts.post_title like "%mars%" AND wp_posts.post_type IN ('post', 'page', 'attachment', 'blog', 'movies') AND wp_term_taxonomy.taxonomy = 'category' AND wp_term_taxonomy.term_id IN (1344,34,36,33,763) AND wp_term_taxonomy.term_id IN (615,616,614,623,1195,620,650,619)
This finds all posts with a title containing "mars" that are in categories 1344 or 34 or 36 or 33 or 763. This part works fine.
The problem is the next part. Since the first category selects a subset of the results only containing categories in the selection, when the second selection runs, there are no results found as term_id is only 1344 or 34 or 36 or 33 or 763.
Another avenue I tried was excluding the joins and selecting based on a subquery to create a list of categories the post is in.
SELECT * FROM wp_posts WHERE wp_posts.post_title like "%mars%" AND wp_posts.post_type IN ('post', 'page', 'attachment', 'blog', 'movies') AND wp_term_taxonomy.taxonomy = 'category' AND (1344,34,36,33,763) IN (SELECT term_taxonomy_id FROM wp_term_relationships WHERE wp_term_relationships.object_id = wp_posts.ID)
However I cannot get the syntax right for the (1344,34,36,33,763) IN part.
Can anybody help with amend these queries so that they work, or recommend an alternative method?
Thanks in advance