1. Advertising
    y u no do it?

    Advertising (learn more)

    Advertise virtually anything here, with CPM banner ads, CPM email ads and CPC contextual links. You can target relevant areas of the site and show ads based on geographical location of the user if you wish.

    Starts at just $1 per CPM or $0.10 per CPC.

Help with complex MySql query, Wordpress database

Discussion in 'MySQL' started by shinycurves, Mar 15, 2013.

  1. #1
    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

    Code (Text):
    1. Group 1              Group 2
    2. [X] Category_1      [ ] Category_4
    3. [X] Category_2      [X] Category_5
    4. [ ] 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:

    Code (Text):
    1. SELECT
    2.     * FROM wp_posts
    3.  
    4. INNER JOIN
    5.     wp_term_relationships ON wp_posts.ID = wp_term_relationships.object_id
    6.  
    7. INNER JOIN
    8.     wp_term_taxonomy ON wp_term_relationships.term_taxonomy_id = wp_term_taxonomy.term_taxonomy_id
    9.  
    10. WHERE
    11.     wp_posts.post_title like "%mars%"
    12.     AND wp_posts.post_type IN ('post', 'page', 'attachment', 'blog', 'movies')
    13.     AND wp_term_taxonomy.taxonomy = 'category'
    14.     AND wp_term_taxonomy.term_id IN (1344,34,36,33,763)
    15.     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.

    Code (Text):
    1. SELECT
    2.     * FROM wp_posts
    3.  
    4. WHERE
    5.     wp_posts.post_title like "%mars%"
    6.     AND wp_posts.post_type IN ('post', 'page', 'attachment', 'blog', 'movies')
    7.     AND wp_term_taxonomy.taxonomy = 'category'
    8.     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. 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
    Last edited: Mar 15, 2013
    shinycurves, Mar 15, 2013 IP
  2. shinycurves

    shinycurves Member

    Messages:
    15
    Likes Received:
    0
    Best Answers:
    0
    Trophy Points:
    36
    #2
    Also asked on http://wordpress.org/support/topic/custom-search-query-complex-category-includes
    shinycurves, Mar 15, 2013 IP
  3. shinycurves

    shinycurves Member

    Messages:
    15
    Likes Received:
    0
    Best Answers:
    0
    Trophy Points:
    36
    #3
    Update with example query results

    Code (Text):
    1. SELECT
    2.     ID,post_title,term_id FROM gal_posts
    3.  
    4. INNER JOIN
    5.     gal_term_relationships ON gal_posts.ID = gal_term_relationships.object_id
    6.  
    7. INNER JOIN
    8.     gal_term_taxonomy ON gal_term_relationships.term_taxonomy_id = gal_term_taxonomy.term_taxonomy_id
    9.  
    10. WHERE
    11.     gal_posts.post_title like "%mars%"
    12.     AND gal_posts.post_type = 'post'
    13.     AND gal_term_taxonomy.taxonomy = 'category' ORDER BY `gal_posts`.`ID` ASC
    From these results you can see how you get multiple results per post, one for each category the post is in.

    Code (Text):
    1. ID    post_title    term_id
    2. 51    Avalanches on Mars    33
    3. 51    Avalanches on Mars    214
    4. 85    Women Drivers on Mars    33
    5. 85    Women Drivers on Mars    614
    6. 149    Phoenix set to land on Mars    33
    7. 149    Phoenix set to land on Mars    218
    8. 167    Phoenix Lands on Mars!    33
    9. 167    Phoenix Lands on Mars!    316
    10. ...
    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
    shinycurves, Mar 15, 2013 IP
  4. Rukbat

    Rukbat Well-Known Member

    Messages:
    2,883
    Likes Received:
    33
    Best Answers:
    51
    Trophy Points:
    100
    #4
    Do you want
    Code (Text):
    1. wp_term_taxonomy.term_id IN (1344,34,36,33,763)
    2. AND wp_term_taxonomy.term_id IN (615,616,614,623,1195,620,650,619)
    or
    Code (Text):
    1. wp_term_taxonomy.term_id IN (1344,34,36,33,763)
    2. OR wp_term_taxonomy.term_id IN (615,616,614,623,1195,620,650,619)
    Rukbat, Mar 15, 2013 IP