Function returning queried meta value based on current post ID

Question

I have written several queries which sums download counts grouped by the post they are associated with, and other relations. They work great for exporting to CSV and other uses outside of WordPress, but now I need to write something that works from within WordPress. I am also using Posts to Posts plugin.

This query which returns download count sums based on directly entering the post id number. This works fine outside of WordPress.

SELECT  r.p2p_from,  p.post_title AS Title,  SUM(m.meta_value) AS Combined_Totals
FROM  wp_postmeta as m 
INNER JOIN wp_posts AS p ON p.ID = m.post_id
INNER JOIN wp_p2p AS r ON m.post_id = r.p2p_to
WHERE  m.meta_key =  '_download_count' AND p.post_type='dlm_download'
AND r.p2p_from ='135'

I am trying to write a function that works like the query above, but dynamically displays the sum of downloads related to the current post, something like "where ID = current post ID" I have looked through the WP Codex and numerous sites. I have written template tags and small functions for WordPress templates before. But I am not sure how to write this one. This is what I have so far:

    public function combined_downloads()
    {
    global $wpdb;
    return $wpdb->get_var(" SELECT SUM( meta_value ) FROM $wpdb->postmeta       
    LEFT JOIN $wpdb->posts on $wpdb->postmeta.post_id = $wpdb->posts.ID
    INNER JOIN 'wp_p2p' on $wpdb->postmeta.post_id = wp_p2p.p2p_to  
    WHERE meta_key = '_download_count'
    AND post_type='dlm_download'            
    AND post_status = 'publish'
    " );

Thank you very much.

Answer 1

You can still do the AS with $wpdb, I find it still makes stuff look cleaner. You can use the $wpdb->prepare() method for sanitation. Are you looking for something like this?

function combined_downloads($post_id)
{
    global $wpdb;
    return $wpdb->get_var($wpdb->prepare(
    "SELECT SUM( meta_value ) FROM $wpdb->postmeta AS m
    LEFT JOIN $wpdb->posts AS p 
       ON m.post_id = p.ID
    INNER JOIN {$wpdb->prefix}p2p AS r 
       ON m.post_id = r.p2p_to   
    WHERE m.meta_key = '_download_count'
    AND p.post_type='dlm_download'            
    AND p.post_status = 'publish'
    AND r.p2p_from = %d
    ", $post_id ));

Then you can call the function from the loop:

$count = combined_downloads($post->ID);

$count will have the result of the query and you can echo it where you like. If you need the function outside the loop, you can do this:

$count = combined_downloads($wp_query->post->ID);