从wp的元值中获取平均数

Question

i've creating following wordpress query which output which is counting the records. However all posts does also have a meta_key with the name betting_odds where the meta_value is something like 2.05, 3.30

how can i add the average values of this meta_key into following query?

SELECT count(DISTINCT $wpdb->postmeta.`post_id`) 
  FROM $wpdb->posts
       LEFT JOIN $wpdb->postmeta ON ($wpdb->posts.ID = $wpdb->postmeta.post_id)
       LEFT JOIN $wpdb->term_relationships ON ($wpdb->posts.ID = $wpdb->term_relationships.object_id)
       LEFT JOIN $wpdb->term_taxonomy ON ($wpdb->term_relationships.term_taxonomy_id = $wpdb->term_taxonomy.term_taxonomy_id) 
 WHERE $wpdb->postmeta.meta_key       = 'betting_status'
    AND $wpdb->postmeta.meta_value    != 'Ikke afgjort'
    AND $wpdb->posts.post_status      = 'publish'
    AND $wpdb->term_taxonomy.taxonomy = 'category'
    AND $wpdb->term_taxonomy.term_id  = 106

Answer 1

This is a common challenge when retrieving data from a key/value store like wp_postmeta.

The trick is to put both the post_id and the meta_key into the ON clause, like so.

(It's also helpful to use table aliases (in this case posts, stat, odds, etc.) in WordPress queries because they get quite a bit more readable.

SELECT count(DISTINCT posts.ID), AVG(odds.meta_value)
  FROM $wpdb->posts posts
  LEFT JOIN $wpdb->postmeta stat
                 ON posts.ID = stat.post_id
                AND stat.meta_key = 'betting_status'
  LEFT JOIN $wpdb->postmeta odds
                 ON posts.ID = odds.post_id
                AND odds.meta_key = 'betting_odds'
  LEFT JOIN $wpdb->term_relationships tr ON posts.ID = tr.object_id
  LEFT JOIN $wpdb->term_taxonomy t ON tr.term_taxonomy_id = t.term_taxonomy_id 
 WHERE stat.meta_value    != 'Ikke afgjort'
   AND posts.post_status      = 'publish'
   AND t.taxonomy = 'category'
   AND t.term_id  = 106

Do you see how we are LEFT JOINing the postmeta table twice, once for each meta value we need?? Do you see how the meta_key match criterion goes into the ON clause, not the WHERE clause? That's the pattern to pull meta values for posts.