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,
        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!

Steve Taylor 4 months 0 Answers 22 views 0

Leave an answer