Custom search query, complex category includes

Hi,

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.

Example

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

Tim