Laravel Eloquent perform Aggregation function in the pivot relationship

I would like to perform the average rating of respective product which stored in the pivot table called "reviews".Below are my existing code.

Product Model:

public function reviews(){
        return $this->belongsToMany(User::class,'reviews')->withPivot('comment','rating')->withTimestamps();
    }
public function getProductRatingAttribute(){
        return $this->reviews()->average('rating');
    }

User Model:

public function reviews(){
        return $this->belongsToMany(Product::class,'reviews')->withPivot('comment','rating')->withTimestamps();
    }

Migration for the reviews

Schema::create('reviews', function (Blueprint $table) {
            $table->id();
            $table->timestamps();
            $table->unsignedBigInteger('user_id');
            $table->unsignedBigInteger('product_id');
            $table->string('comment')->nullable();
            $table->integer('rating');
            $table->foreign('user_id')->references('id')->on('users');
            $table->foreign('product_id')->references('id')->on('products');
        });

I have attempted to use the below to code construct the expected output but it leads N+1 issue because it does not take advantage of the eager loading of the Laravel Eloquent.

$s = Product::with('reviews')->append('product_rating');

As I check telescope, it produces 6 query which can lead performance issue when the data is large.

Expected output:

{
  "msg": [
    {
      "id": 4,
      "created_at": "2021-04-09T07:32:35.000000Z",
      "updated_at": "2021-04-09T07:32:35.000000Z",
      "name": "MacDonald",
      "nickname": "MCD",
      "users_id": 1,
      "price": "0.00",
      "product_rating": "3.3333",
      "reviews": [
        {
          "id": 1,
          "name": "John Smith",
          "email": "john.smith@hotmail.com",
          "email_verified_at": null,
          "created_at": "2021-04-08T13:29:13.000000Z",
          "updated_at": "2021-04-08T13:29:13.000000Z",
          "role": 0,
          "pivot": {
            "product_id": 4,
            "user_id": 1,
            "comment": "Ouch",
            "rating": 3,
            "created_at": "2021-05-01T11:51:26.000000Z",
            "updated_at": "2021-05-01T11:52:07.000000Z"
          }
        },
        {
          "id": 2,
          "name": "Kelvin Ooi",
          "email": "kelvin.ooi@hotmail.com",
          "email_verified_at": null,
          "created_at": "2021-04-08T13:29:13.000000Z",
          "updated_at": "2021-04-13T12:07:11.000000Z",
          "role": 1,
          "pivot": {
            "product_id": 4,
            "user_id": 2,
            "comment": "Ouch",
            "rating": 5,
            "created_at": "2021-05-01T11:51:26.000000Z",
            "updated_at": "2021-05-01T11:52:07.000000Z"
          }
        },
        {
          "id": 1,
          "name": "John Smith",
          "email": "john.smith@hotmail.com",
          "email_verified_at": null,
          "created_at": "2021-04-08T13:29:13.000000Z",
          "updated_at": "2021-04-08T13:29:13.000000Z",
          "role": 0,
          "pivot": {
            "product_id": 4,
            "user_id": 1,
            "comment": "Ouch",
            "rating": 2,
            "created_at": "2021-05-01T11:51:26.000000Z",
            "updated_at": "2021-05-01T11:52:07.000000Z"
          }
        }
      ]
    },
    {
      "id": 10,
      "created_at": null,
      "updated_at": null,
      "name": "Mary Bown",
      "nickname": "MB",
      "users_id": 1,
      "price": "2.88",
      "product_rating": "2.0000",
      "reviews": [
        {
          "id": 1,
          "name": "John Smith",
          "email": "john.smith@hotmail.com",
          "email_verified_at": null,
          "created_at": "2021-04-08T13:29:13.000000Z",
          "updated_at": "2021-04-08T13:29:13.000000Z",
          "role": 0,
          "pivot": {
            "product_id": 10,
            "user_id": 1,
            "comment": "Ouch",
            "rating": 2,
            "created_at": "2021-05-01T11:51:26.000000Z",
            "updated_at": "2021-05-01T11:52:07.000000Z"
          }
        }
      ]
    },
    {
      "id": 11,
      "created_at": null,
      "updated_at": null,
      "name": "Pizza Hut",
      "nickname": "pizzahut",
      "users_id": 1,
      "price": "4.10",
      "product_rating": null,
      "reviews": [
        
      ]
    },
    {
      "id": 12,
      "created_at": "2021-04-09T08:00:42.000000Z",
      "updated_at": "2021-04-09T08:00:42.000000Z",
      "name": "Domino Pizza",
      "nickname": "domino",
      "users_id": 3,
      "price": "0.00",
      "product_rating": null,
      "reviews": [
        
      ]
    },
    {
      "id": 13,
      "created_at": "2021-04-26T16:12:53.000000Z",
      "updated_at": "2021-04-26T16:12:53.000000Z",
      "name": "Chicken Chop",
      "nickname": "chickchop",
      "users_id": 3,
      "price": "0.00",
      "product_rating": null,
      "reviews": [
        
      ]
    }
  ]
}

from Newest questions tagged laravel-5 - Stack Overflow https://ift.tt/3ubOoNc
via IFTTT