Query for average of enum column returns incorrect value

[dauger27]

I'm attempting to implement functionality that would determine the average rating of a restaurant. I have a ratings table (multiple tables join users and restaurants so I used custom names to reduce confusion) which has restaurant_id and rating columns.

I've written out an averaging loop which is working as expected, but when I attempt to use the build-in avg command in Laravel it returns with incorrect data. This rating column is an enum type to enforce specific values (1, 1.5, ... 4.5, 5) so I'm not certain if that's what is causing this issue to occur. The // <number> indicates what I'm receiving from those outputs.

$sum = 0;
$ratings = self::where('restaurant_id', $restaurant_id)->get();
foreach ($ratings as $r) {
    dump($r->rating); // 3.5 & 2.5
    $sum += $r->rating;
}
dump($sum/$ratings->count()); // 3

$average = self::where('restaurant_id', $restaurant_id)->avg('rating');
dd($average); // 5

I've also tried including the groupBy and select queries but with no success. I tested the other aggregates sum, max, and min. I got a value of 10 back from sum but the expected 3.5 from max and 2.5 from min. That's why I'm worried that the sum and avg don't play well with enum fields potentially.

[dauger27]

As a test, I created an additional column rating_d that was set up as:

$table->decimal('rating_d', 2, 1); // (2 precision digits and 1 scale digit, ie. X.X)

After populating the column with the same data from rating, I adjusted my avg('rating') to use that new column and verified that I got back a "3.00000" from Laravel. It definitely appears that using the enum column type caused the avg and sum aggregates to break.

[susanBuck]

Hi @dauger27 -

This following line is missing a method to execute the query- it should include get:

# Before:
$average = Rating::where('restaurant_id', $restaurant_id)->avg('rating');

# After:
$average = Rating::where('restaurant_id', $restaurant_id)->get()->avg('rating');

That will yield a collection which avg can then be used on with expected results.