how to use groupBy in hasMany relation in Laravel

Question

I have 2 tables that are named Resort and booking. in the booking table, there is a field named amount. I want to join these tables using with hasMany relation and get sum of the amount field in the booking table using with groupBy. can you please help me to solve this problem?

Thanks in Advance

Answer 1

Laravel Eloquent has the own withSum() method for avoiding "groupBy()" method.

For your case you can use something like this (you can modify for your needs):

// resorts with bookings and their summary prices
$data = Resort::select([
    'id',
    'name',
    'image',
])
    // ->orderBy('some_field', 'ASC')
    ->with(['bookings' => function($query) {
        return $query->select([
            'id',
            'booking_id',
            'price',
        ]);
    }])
    ->withSum('bookings', 'price')
    // ->where('resorts.some_field', '<=', 123)
    ->get();
    // ->toArray();

But don't forget to have appropriate relation defined in your parent (Resort) model:

public function bookings() {
    return $this->hasMany(Booking::class, 'resort_id', 'id');
}

Also you need to have "resort_id" foreign key defined in child's (Booking) migration:

$table->unsignedTinyInteger('resort_id')->nullable();
$table->foreign('resort_id')->references('id')
    ->on('resorts'); // ->onUpdate('cascade')->onDelete('cascade');