WordPress.org

Ready to get started?Download WordPress

Forums

Custom search query, complex category includes (1 post)

  1. Azulia Designs
    Member
    Posted 1 year ago #

    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

Topic Closed

This topic has been closed to new replies.

About this Topic