0

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.

0

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);
  • Thank you very much, @GentlemanMax. This works perfectly within the loop (I put the call in single.php) and on areas outside of the loop (we are working on a dashboard widget). I appreciate also the syntax $wpdb->prefix to mask the prefix of the Post to Posts table. I will be doing a lot more reading on WordPress syntax for sure. Thanks again. – rootl Apr 14 '15 at 14:05
  • No problem, I'm glad it helped. Don't forget to mark the question as answered :) – TheGentleman Apr 14 '15 at 18:03

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.