[wp-hackers] SQL: pulling WP data by multiple meta_key/value pairs

[Otto]

On Wed, Jan 16, 2013 at 9:49 AM, Kevinjohn Gallagher
<kevinjohngallagher at hotmail.com> wrote:
> I have a small issue that I think is probably easy to solve and just shows my poor Dev skills (and googling skills).
>
> 1. I need to have an SQL query that returns posts based on 2 (or more different) meta_key/value pairs.
>
> I also need to order this by a 3rd meta_key/value pair.
>
> (This needs to be a SQL query  as it's done outside of WP)

Hard to be efficient in writing a query without more info than this,
but a generic way that will work is to use multiple JOINs.

SELECT whatever FROM wp_posts
JOIN wp_postmeta a ON ( a.post_id = wp_posts.ID AND a.meta_key='whatever-a' )
JOIN wp_postmeta b ON ( b.post_id = wp_posts.ID AND b.meta_key='whatever-b' )
JOIN wp_postmeta c ON ( c.post_id = wp_posts.ID AND c.meta_key='whatever-c' )
WHERE
a.meta_value = 'value-a' AND
b.meta_value = 'value-b'
ORDERBY c.meta_value

This may be slow sometimes, and could be optimized if the question was
more specific.

-Otto