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

[Kevinjohn Gallagher]

Thank you for the replies Sam, Simon and Simon.
While efficiency is obviously important, the number of users and caching right now is at a level where I can live with it for another month.
We're facing a situation where Wordpress is a great tool for the editors to use to select the data needed from 5 - 10 CPTs, and then our front of house staff use a single CPT that effectively is just a selection of the data needed from the other CPTs (via a drop downs). It basically means though that all of the data is in meta_data, given that the 1:1 nature of posts isn't scale-able (things like having a Title as mandatory? that's not really needed in a CMS built to share data in a modular way where multiple data parts make up the whole).
I was hoping to avoid the multiple join route, but it does appear to be the way to go right now.
When I have some stats and the specifics around the before and after I'll come back. You folks can probably spot quite a few improvements.
Thanks again,Kev



> From: otto at ottodestruct.com
> Date: Wed, 16 Jan 2013 16:35:28 -0600
> To: wp-hackers at lists.automattic.com
> Subject: Re: [wp-hackers] SQL: pulling WP data by multiple meta_key/value	pairs
> 
> 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
> _______________________________________________
> wp-hackers mailing list
> wp-hackers at lists.automattic.com
> http://lists.automattic.com/mailman/listinfo/wp-hackers