PHP | left join avg query give me Array()

Question

I'm trying to implement a query within my code that gives me the ability to calculate the average value between the ratings received and the number of ratings per post, and sort posts from the one with the highest rating to to the one with the lowest average rating

looking at the following table and implementing this query everything works perfectly.

   <?php
    $results = $wpdb->get_results("
    SELECT comment_id, avg(meta_value) avg_meta_value
    FROM {$wpdb->prefix}commentmeta 
    
    
    WHERE {$wpdb->prefix}commentmeta.meta_key = 'rating'
    GROUP BY comment_id 
    ORDER BY avg_meta_value desc");
    foreach ($results as $result)
    {
        echo $result->comment_id.'<br>';
    }

the problem happens when I want to get a left join with the comments table:

even if the query is correct:

        <?php
        $results = $wpdb->get_results("
        SELECT comment_id, avg(meta_value) avg_meta_value
        FROM {$wpdb->prefix}commentmeta 
        
        LEFT JOIN $wpdb->prefix}comments
        ON {$wpdb->prefix}commentmeta.comment_id = $wpdb->prefix}comments.comment_ID
        
        
        WHERE {$wpdb->prefix}commentmeta.meta_key = 'rating'
        GROUP BY comment_id 
        ORDER BY avg_meta_value desc");
        
        foreach ($results as $result)
        {
            echo $result->comment_id.'<br>';
        }
        ?>

doing the print_r($ results); i get: Array( )

and I don't understand why, understanding this step is essential for me to make the next join with the post table, so that I can get all the values ​​out, why do I get array ()?

how can i fix it?

.... and

var_dump($result); return me `NULL`

Answer 1

You have a mistake in your query

      $sql = "SELECT 
                    {$wpdb->prefix}commentmeta.comment_id, avg({$wpdb->prefix}commentmeta.meta_value) avg_meta_value
                FROM 
                    {$wpdb->prefix}commentmeta
                LEFT JOIN 
                    {$wpdb->prefix}comments
                ON 
                    {$wpdb->prefix}commentmeta.comment_id = {$wpdb->prefix}comments.comment_ID
                WHERE 
                    {$wpdb->prefix}commentmeta.meta_key = 'rating'
                GROUP BY 
                    {$wpdb->prefix}commentmeta.comment_id 
                ORDER BY 
                    avg_meta_value desc";
                    
        $results = $wpdb->get_results($sql);
        
        foreach ($results as $result)
        {
            echo $result->comment_id.'<br>';
        }