Support » Plugins and Hacks » Hacks » Custom query, Multiple IN clause

  • Resolved wp_joost

    (@wp_joost)


    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 🙂

Viewing 1 replies (of 1 total)
Viewing 1 replies (of 1 total)
  • The topic ‘Custom query, Multiple IN clause’ is closed to new replies.