Can Anyone provide an example of RAW SQL for SELECTING posts by 2 or more tags

Question

I need a RAW SQL, as I am working from different platform with WordPress Database.

When I run example.com/tag/first+second, WordPress runs a query SELECT post_id, meta_key, meta_value FROM wp_postmeta WHERE post_id IN (10649,7783,6336,4556,3938)

Where did this get the post IDs? I am only guessing. I do have certain thoughts about it, but first want to see if someone knows.

Here is the query I have built when getting posts by SINGLE tag:

SELECT `wp_posts`.`ID`, `wp_posts`.`post_name`, `wp_posts`.`post_title`, `wp_posts`.`post_date`, `wp_posts`.`post_content` , DATEDIFF(CURDATE(),post_date) as days_ago 
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)
    INNER JOIN wp_terms 
        ON (wp_term_taxonomy.term_id = wp_terms.term_id)
WHERE 1=1
    AND wp_term_taxonomy.taxonomy = 'post_tag'
    AND wp_terms.slug SOUNDS LIKE 'Hennessy'
    AND wp_terms.slug SOUNDS LIKE 'VS' 
    AND wp_posts.post_type = 'post'
    AND (wp_posts.post_status = 'publish')
GROUP BY wp_posts.ID
ORDER BY wp_posts.post_date DESC

thanks.

Answer 1

You seem to be catching wrong query. Dumping SQL for such URL (via posts_request filter) get me this:

SELECT SQL_CALC_FOUND_ROWS  wp_posts.* 
FROM wp_posts  
INNER JOIN wp_term_relationships ON (wp_posts.ID = wp_term_relationships.object_id) 
INNER JOIN wp_term_relationships AS tt1 ON (wp_posts.ID = tt1.object_id) 
WHERE 1=1  
AND ( wp_term_relationships.term_taxonomy_id IN (93) AND tt1.term_taxonomy_id IN (94) AND wp_posts.ID IN (
                    SELECT object_id
                    FROM wp_term_relationships
                    WHERE term_taxonomy_id IN (93,94)
                    GROUP BY object_id HAVING COUNT(object_id) = 2
                ) ) 
AND wp_posts.post_type = 'post' 
AND (wp_posts.post_status = 'publish' OR wp_posts.post_status = 'private') 
GROUP BY wp_posts.ID ORDER BY wp_posts.post_date DESC LIMIT 0, 10

In any case see WP_Query->get_posts() method, which handles turning query variables into SQL. There is a lot of code there and a lot of it was changed in 3.1, so it's a little hard for me to pinpoint where multiple tags are handled.