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

us flag

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')

$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?

ph flag

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.

cn flag

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.

us flag
My goal here is to get the Average rating and to minimize the use of memory. Your answer doesn't address this, Lets assume that there's a million comments, Getting all the comments will result to error `Allowed Memory Size Exhausted`. I want to get the average rating in a query level. Like querying like this `SELECT AVG(Price) AS AveragePrice FROM Products;`
Jean da Silva avatar
cn flag
I see. Then, if I understood it right, can't you make a dynamic query to return a SUM of all field_ratings, and another dynamic query to return the count of all comments, then in PHP use both values to get the average? If it isn't possible to sum and count every registry of the table, then can you do it in batch? Count the number of registries and separate then in batches. Either way you can retrieve only the sum or the field_rating if you wish instead of loading every entity.

Post an answer

Most people don’t grasp that asking a lot of questions unlocks learning and improves interpersonal bonding. In Alison’s studies, for example, though people could accurately recall how many questions had been asked in their conversations, they didn’t intuit the link between questions and liking. Across four studies, in which participants were engaged in conversations themselves or read transcripts of others’ conversations, people tended not to realize that question asking would influence—or had influenced—the level of amity between the conversationalists.