0

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

db value

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:

db value

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`

1 Answer 1

0

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>';
        }
2
  • thankyou, onyl last question if i impelement another join with posts: LEFT JOIN {$wpdb->prefix}posts ON {$wpdb->prefix}comments.comment_post_ID = {$wpdb->prefix}posts.ID and i retrive $result->post_title, i don t have anything Oct 26, 2020 at 17:04
  • because the goal is to have access to the posts table, to sort the posts with the highest number of ratings up to the lowest, but I can't access the posts table Oct 26, 2020 at 17:13

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.