WordPress.org

Ready to get started?Download WordPress

Forums

[resolved] Custom query, Multiple IN clause (2 posts)

  1. wp_joost
    Member
    Posted 1 year ago #

    Hello, i'm stuck on a problem with my query.

    First i had this query to get posts based on selected categories.

    SELECT SQL_CALC_FOUND_ROWS posts.ID, posts.post_title FROM posts
    INNER JOIN term_relationships ON (posts.ID = term_relationships.object_id) 
    
    WHERE 1=1
    AND ( SELECT COUNT(1) FROM term_relationships WHERE term_taxonomy_id IN (7) AND object_id = posts.ID ) = 1
    AND ( SELECT COUNT(1) FROM term_relationships WHERE term_taxonomy_id IN (16,17) AND object_id = posts.ID ) = 1 
    
    AND posts.post_type = "post"
    AND posts.post_status = "publish"
    GROUP BY posts.ID
    ORDER BY posts.post_date
    DESC

    The 2 end clauses there are dynamicly added, so with this query it selects posts that are in ( category 7 ) AND in ( category 16 or 17 )
    This works great... But then i found out the query doesn't work when custom taxonomies are used, so now i'm stuck with the query below;

    SELECT ID, post_title FROM posts
    LEFT JOIN term_relationships ON(posts.ID = term_relationships.object_id)
    LEFT JOIN term_taxonomy ON(term_relationships.term_taxonomy_id = term_taxonomy.term_taxonomy_id)
    LEFT JOIN terms ON(term_taxonomy.term_id = terms.term_id)
    
    WHERE 1=1
    AND terms.term_id IN (7)
    AND terms.term_id IN (16,17)
    
    AND term_taxonomy.taxonomy = 'category'
    AND posts.post_status = 'publish'
    AND posts.post_type = 'post'

    When i use the AND .... IN() more than once it stops returning anything. When i do it once, it works.

    I hope someone can help me out :)

  2. wp_joost
    Member
    Posted 1 year ago #

    Problem solved, problem was something else.

Topic Closed

This topic has been closed to new replies.

About this Topic