Help with complex MySql query, Wordpress database

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

Code (markup):

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)

Code (markup):

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)

Code (markup):

However I cannot get the syntax right for the (1344,34,36,33,763) IN part. I know that its poor for performance, and it didn't work so I didn't pursue this avenue very far.

Can anybody help with amend these queries so that they work, or recommend an alternative method?

If you are not familiar with the Wordpress database structure, you can see the database description on the Wordpress Codex - http://codex.wordpress.org/Database_Description

Thanks in advance

Tim

 

Also asked on http://wordpress.org/support/topic/custom-search-query-complex-category-includes

 

Update with example query results

SELECT
    ID,post_title,term_id FROM gal_posts
 
INNER JOIN
    gal_term_relationships ON gal_posts.ID = gal_term_relationships.object_id
 
INNER JOIN
    gal_term_taxonomy ON gal_term_relationships.term_taxonomy_id = gal_term_taxonomy.term_taxonomy_id
 
WHERE
    gal_posts.post_title like "%mars%"
    AND gal_posts.post_type = 'post'
    AND gal_term_taxonomy.taxonomy = 'category' ORDER BY `gal_posts`.`ID` ASC

Code (markup):

From these results you can see how you get multiple results per post, one for each category the post is in.

ID    post_title    term_id
51    Avalanches on Mars    33
51    Avalanches on Mars    214
85    Women Drivers on Mars    33
85    Women Drivers on Mars    614
149    Phoenix set to land on Mars    33
149    Phoenix set to land on Mars    218
167    Phoenix Lands on Mars!    33
167    Phoenix Lands on Mars!    316
...

Code (markup):

When `AND wp_term_taxonomy.term_id IN (1344,34,36,33,763)` is added to the query you only get 4 results (from this subset) where term_id = 33. The second `AND wp_term_taxonomy.term_id IN (615,616,614,623,1195,620,650,619)` returns no results.

Expected result in this case would be ID = 85

Thanks

 

Do you want

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)

Code (markup):

or

wp_term_taxonomy.term_id IN (1344,34,36,33,763)
OR wp_term_taxonomy.term_id IN (615,616,614,623,1195,620,650,619)

Code (markup):