Can I eager/lazy load an attribute in Laravel?

Question

I know I can eager or lazy load a relationship in Laravel. I also know that the relationship objects are basically query objects in disguise, and that calling $user->load('teams') executes that query and adds it to the $user object (even if I don't know how exactly.)

Say I have a User who has gets points from comments. To get the user's points in a database call, I would execute something like this:

SELECT `user_id`, sum(`points`) AS `total_points` FROM `user_comments` where `id` = ?

If I wanted to load them all at once, I would run this query:

SELECT `users`.*, sum(`user_comments`.`points`) AS `total_points`
FROM `users` LEFT JOIN `user_points` ON `users`.`id` = `user_comments`.`user_id`
GROUP BY `users`.`id`

I want to use Laravel to accomplish those tasks. I would like to write something like this

User::where('age', '>', 40)->with('total_points')->get();

and access the value like this

$user->total_points

to load the users and their total scores at the same time. I expect that there might be many, many records and don't want to always call the DB join query each time I have a user. I would also prefer not to load all the comments themselves for each user if I only need their total point value.

Is there a way to use a relationship builder to accomplish this in Laravel?

Answer 1

Make one relationship in your User Model like below

public function total_points() {
        return $this->hasOne('user_comments')
            ->selectRaw('sum(points) as points, user_id')
            ->groupBy('user_id');
    }

Then do this in controller

$user = User::where('age', '>', 40)->with('total_points')->get();