Take the 2-minute tour ×
WordPress Development Stack Exchange is a question and answer site for WordPress developers and administrators. It's 100% free, no registration required.

I have a site with custom roles, and custom taxonomies. I'm making the advanced search, where custom posts can be filtered by roles and taxonomies.

To accomplish this, I'm using my own custom SQL query, which works fine with the roles, and one taxonomy, but I'm having a hard time getting it to work with a second taxonomy (I get no results). Here's the code I'm generating (with some sample values coming in from the form):

SELECT DISTINCT p.* FROM wp_posts p 
LEFT JOIN wp_usermeta um ON p.post_author = um.user_id 
LEFT JOIN wp_term_relationships txr ON p.ID = txr.object_id 
LEFT JOIN wp_term_taxonomy tx ON txr.term_taxonomy_id = tx.term_taxonomy_id 
LEFT JOIN wp_terms trm ON tx.term_id = trm.term_id 
LEFT JOIN wp_terms trk ON tx.term_id = trk.term_id 
WHERE (um.meta_key = 'wp_capabilities' AND (um.meta_value LIKE '%student%' OR um.meta_value LIKE '%instructor%' )) 
AND (tx.taxonomy= 'mediums' AND ( trm.name LIKE '%acrylic%' OR trm.name LIKE '%oil%' )) 
AND ( tx.taxonomy= 'keywords' AND ( trk.name LIKE '%landscape%' OR trk.name LIKE '%test%')) 
AND p.post_status = 'publish' 
AND p.post_type = 'gallery' 
GROUP BY p.ID 
ORDER BY p.post_date DESC

When I input this directly into PHPMyAdmin, I get no errors, but the thing just hangs ("Loading message").

What am I doing wrong?

share|improve this question
    
were you able to solve it? –  Amit Jul 5 '13 at 15:23
    
Yes, I was. I'll post the answer shortly. –  coopersita Jul 8 '13 at 15:28

1 Answer 1

I was able to solve my problem with this query, although I bet it can be more efficient (I just couldn't figure out how to do it.)

SELECT DISTINCT p.* FROM wp_posts p 
LEFT JOIN wp_usermeta um ON p.post_author = um.user_id 
LEFT JOIN wp_postmeta pm ON p.ID = pm.post_id 
INNER JOIN ( 
    SELECT txrm.object_id FROM wp_term_relationships AS txrm 
    LEFT JOIN wp_term_taxonomy txm ON txrm.term_taxonomy_id = txm.term_taxonomy_id 
    LEFT JOIN wp_terms trm ON txm.term_id = trm.term_id 
    WHERE txm.taxonomy = 'mediums' 
    AND ( trm.name LIKE '%Acrylic%' ) 
    GROUP BY txrm.object_id 
    HAVING count(trm.name) = 1 
) AS trm ON p.ID = trm.object_id 
INNER JOIN wp_term_relationships txrk ON p.ID = txrk.object_id 
INNER JOIN wp_term_taxonomy txk ON txrk.term_taxonomy_id = txk.term_taxonomy_id 
INNER JOIN ( 
    SELECT txrk.object_id FROM wp_term_relationships AS txrk 
    LEFT JOIN wp_term_taxonomy txk ON txrk.term_taxonomy_id = txk.term_taxonomy_id 
    LEFT JOIN wp_terms trk ON txk.term_id = trk.term_id 
    WHERE txk.taxonomy = 'keywords' 
    AND ( trk.name LIKE '%Landscape%' ) 
    GROUP BY txrk.object_id 
    HAVING count(trk.name) = 1 
) AS trk ON p.ID = trk.object_id 
WHERE p.post_status = 'publish' 
AND p.post_type = 'gallery' 
GROUP BY p.ID 
ORDER BY p.post_date DESC 

So basically, I had to add an inner query for each taxonomy, in which it sends back a list of term relationships that match the criteria, but by grouping them, I don't get duplicates.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.