Get nested relationship count and column sum

You can use Laravel's Eloquent ORM to define relationships between the tables and then use eager loading to retrieve the required data. Here's an example:

First, define the relationships in your models:

// User.php
public function foo()
{
    return $this->hasOne(Foo::class);
}

// Foo.php
public function bar()
{
    return $this->hasOne(Bar::class);
}

// Bar.php
public function records()
{
    return $this->hasMany(Record::class);
}

Then, you can use eager loading to retrieve the required data:

$users = User::with(['foo.bar.records' => function ($query) {
    $query->selectRaw('bar_id, count(*) as count, sum(amount) as total_amount')
          ->groupBy('bar_id');
}])->get(['id', 'last_name']);

foreach ($users as $user) {
    $count = 0;
    $totalAmount = 0;

    if ($user->foo && $user->foo->bar) {
        $count = $user->foo->bar->records->sum('count');
        $totalAmount = $user->foo->bar->records->sum('total_amount');
    }

    echo "{$user->id}, {$user->last_name}, {$count}, {$totalAmount}\n";
}

In the above code, we're using the with method to eager load the foo.bar.records relationship. We're also using a closure to select the required columns and perform the aggregation.

Finally, we're looping through the users and calculating the count and total amount of records for each user.