1

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?

2 Answers 2

1
+50

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.

0

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.

2
  • 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; May 21 at 1:28
  • 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. May 23 at 14:08

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.