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
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.