How can I get Average of a comment field by node_id?

Question

I have Comments here wherein user can rate a content.

I want to get the average rating per content, how do I efficiently get the average?

I have the following code to get the Average:

$cids = \Drupal::entityQuery('comment')
    ->condition('entity_id', $variables['row']->nid)
    ->condition('entity_type', 'node')
    ->condition('comment_type', 'content_rating')
    ->execute();

$comments = [];

$totalRating = 0;

if ($cids) {
    foreach ($cids as $cid) {
        $comment = Comment::load($cid);
        $comments[] = $comment->get('field_rating')->value;
    }

    $totRating = array_sum($comments) / count( $comments );

    $totalRating = ceil($totRating);
}

I know this is not efficient because I'm getting ALL the comments and manually computing for the average rating.

How can I get the average rating on a query level?

Answer 1

You should have a table in your database named something like comment__field_rating. That table should have a column like field_rating_value. Once you've confirmed that, you can write a query something like this:

$q = $database->db_select('comment__field_rating)
$q->addExpression('avg(field_rating_value)', 'avg_rating')
$avg = $q->execute()->fetchCol();

That's all untested because I don't have your database.

Answer 2

In the docs about the Entity API, there is a method from EntityStorage that you can use to load an array of entity IDs, the loadMultiple($id). Then you can loop through the array of Comments and get all field_ratings like you are already doing.

// Load multiple entities, also exists as entity_load_multiple().
$entities = \Drupal::entityTypeManager()->getStorage($entity_type)
  ->loadMultiple([1, 2, 3]);

Also, instead of loading \Drupal::entityTypeManager(), you can inject the service 'entity_type.manager' inside the constructor of your class. This is the best way to call it, but the first one will work ok.