Most efficient way to get posts with postmeta
I need to get a bunch of posts with their metadata. Of course you can’t get metadata with a standard posts query, so you generally have to do a
get_post_custom() for each post.
I’m trying with one custom query, like this:
$results = $wpdb->get_results(" SELECT p.ID, p.post_title, pm1.meta_value AS first_field, pm2.meta_value AS second_field, pm3.meta_value AS third_field FROM $wpdb->posts p LEFT JOIN $wpdb->postmeta pm1 ON ( pm1.post_id = p.ID AND pm1.meta_key = 'first_field_key' ) LEFT JOIN $wpdb->postmeta pm2 ON ( pm2.post_id = p.ID AND pm2.meta_key = 'second_field_key' ) LEFT JOIN $wpdb->postmeta pm3 ON ( pm3.post_id = p.ID AND pm3.meta_key = 'third_field_key' ) WHERE post_status = 'publish' ");
Seems to work. It trips up if you use any of those meta fields in a way that allows multiple meta values for it on the same post. I can’t think of a join to do that.
So, question 1: Is there a join, sub-query, or whatever, to bring in multiple-value meta fields?
But question 2: Is it worth it? How many
postmeta table joins do I add before a 2-query approach becomes preferable? I could grab all post data in one query, then grab all relevant postmeta in another, and combine the meta with the post data in one resultset in PHP. Would that end up being quicker than an single ever-more-complex SQL query, if that’s even possible?
I always think, “Give as much work as possible to the database.” Not sure on this one!