TransWikia.com

How Can i fetch all data with average rating in laravel

Stack Overflow Asked by Nadun Silva on December 12, 2020

I want to display all the products with their average rating. In this case, I Have two tables like Product Table and Product Rating Table

For example:

My Products table

ProductID  | ProductName
-------------------------
1          | ABC Product
2          | XYZ Product
3          | LMN Product

My Rating Table

   ID   | ProductID  | Value
    -------------------------
    1   | 1          | 4
    2   | 1          | 5
    3   | 2          | 3

I need to get output like this,

 [
    {
     "ProductID": 1,
     "ProductName": ABC Product,
     "AverageRating": 4.5,
    },

    {
     "ProductID": 2,
     "ProductName": XYZ Product,
     "AverageRating": 3.0,    
    },  

   {
     "ProductID": 3,
     "ProductName": LMN Product,  
     "AverageRating": 0,
    }

 ]

How can I get this output in laravel? Thanks in advance!

One Answer

$data = DB::table('products_table')
->select('products_table.ProductId', 
         'products_table.ProductName', 
         DB::raw('AVG(ratings_table.value) as AverageRating')
->leftJoin('ratings_table', 'ratings_table.ProductId', 'products_table.ProductId')
->groupBy('ratings_table.ProductId')->get();

return $data->toArray();

I haven't run this query, but this might give you an idea for your questions.

Correct answer by Jayant on December 12, 2020

Add your own answers!

Ask a Question

Get help from others!

© 2024 TransWikia.com. All rights reserved. Sites we Love: PCI Database, UKBizDB, Menu Kuliner, Sharing RPP