I need help with a complex database query for my WordPress site. I created a custom taxonomy system where posts get multiple categories - some for content topics (like birds, fish, elephants) and others for post types (like expert, company, blog post).
I want to find posts that match specific criteria: posts about birds OR fish AND posts that are companies. Basically I need something like IN (birds, fish) AND IN (company) but I can’t get the SQL syntax right.
Here’s my current query but it’s not working:
SELECT * FROM $wpdb->posts
LEFT JOIN $wpdb->postmeta ON($wpdb->posts.ID = $wpdb->postmeta.post_id)
LEFT JOIN $wpdb->term_relationships ON($wpdb->posts.ID = $wpdb->term_relationships.object_id)
LEFT JOIN $wpdb->term_taxonomy ON($wpdb->term_relationships.term_taxonomy_id = $wpdb->term_taxonomy.term_taxonomy_id)
WHERE $wpdb->term_taxonomy.term_id = 4,5,6
AND $wpdb->term_taxonomy.taxonomy = 'category'
AND $wpdb->posts.post_status = 'publish'
ORDER BY $wpdb->postmeta.meta_value ASC
What’s the correct way to structure this query for multiple category matching?
You’ve got a syntax error in your WHERE clause - term_id = 4,5,6 should use the IN operator. But honestly, that’s not your main problem. Your whole approach with direct SQL queries is gonna bite you later. I learned this the hard way when building similar taxonomy filters - they become a nightmare to maintain. WordPress already handles this stuff through built-in functions. Just use get_posts() with a tax_query parameter instead. It’ll generate the proper SQL with JOINs and handle all the edge cases like post revisions and meta queries automatically. Performance is basically the same for most sites, but maintainability is way better. You get caching and security sanitization built-in without writing custom prepared statements. Plus your current query will break if the taxonomy structure changes or you need more complex conditions down the road.
actually, there’s another issue - your LEFT JOINs should be INNER JOINs for category filtering. LEFT JOINs return posts without categories too, which probably isn’t what you want. also, that postmeta join looks unnecessary unless you’re ordering by specific meta fields?
Your SQL approach is getting messy fast. I’ve dealt with similar filtering before - trust me, query complexity explodes when you add more categories.
The main problem is your WHERE clause syntax. You can’t do term_id = 4,5,6 like that. You’d need:
SELECT DISTINCT p.* FROM wp_posts p
INNER JOIN wp_term_relationships tr1 ON p.ID = tr1.object_id
INNER JOIN wp_term_taxonomy tt1 ON tr1.term_taxonomy_id = tt1.term_taxonomy_id
INNER JOIN wp_term_relationships tr2 ON p.ID = tr2.object_id
INNER JOIN wp_term_taxonomy tt2 ON tr2.term_taxonomy_id = tt2.term_taxonomy_id
WHERE (tt1.term_id IN (4,5) AND tt1.taxonomy = 'category')
AND (tt2.term_id = 6 AND tt2.taxonomy = 'category')
AND p.post_status = 'publish'
Honestly though, maintaining these complex queries is a nightmare. Every time you want different filtering logic, you’re back debugging SQL.
I switched to automating this entirely. Set up workflows that handle filtering without touching database queries directly. Way cleaner, and you can modify logic through a visual interface instead of wrestling with JOINs.
You can easily add new filtering conditions or connect other data sources without rewriting everything.
Been there with complex taxonomy queries. Yeah, your main issue is the term_id = 4,5,6 syntax - that’s not valid SQL. But honestly, fixing the SQL is just slapping a bandage on a bigger mess.
Every time filtering requirements change, you’re back debugging JOINs and performance issues. I’ve watched this pattern kill productivity on tons of projects.
Build this as an automated workflow instead of hardcoded queries. Create logic flows that handle filtering without touching SQL directly. Need to add categories or change OR/AND logic? Just modify the workflow visually.
You can easily connect other data sources later or trigger actions based on filtered results. Auto-update related posts or send notifications when new matches appear.
I’ve set up similar taxonomy filtering this way - way more maintainable. No more wrestling with complex JOINs every time requirements change.
Your problem is using a comma-separated list with the equals operator. WordPress needs multiple JOINs to handle AND conditions between different category groups. I hit this exact issue last year building a directory site. Here’s what works: sql SELECT DISTINCT p.* FROM wp_posts p INNER JOIN wp_term_relationships tr1 ON p.ID = tr1.object_id INNER JOIN wp_term_taxonomy tt1 ON tr1.term_taxonomy_id = tt1.term_taxonomy_id INNER JOIN wp_term_relationships tr2 ON p.ID = tr2.object_id INNER JOIN wp_term_taxonomy tt2 ON tr2.term_taxonomy_id = tt2.term_taxonomy_id WHERE tt1.term_id IN (4,5) AND tt1.taxonomy = 'category' AND tt2.term_id = 6 AND tt2.taxonomy = 'category' AND p.post_status = 'publish' You need separate JOIN pairs for each condition group. One handles your topic categories (birds/fish), another handles your type category (company). Without this, WordPress can’t tell OR from AND logic. Better yet, use WP_Query with tax_query arrays - way cleaner and easier to debug when things change.