laravel use closure with relationship

Question

Assume I have a Group and Student model, it's a one-to-many relationship;

Group has many Student,every Student has id and tuition attribute.

So I want get Group with students numbers and all the tuition .

Here is my code:

Group::with(['student'=>function($query){
        $query->select(DB::raw('count(`id`) as numbers, sum(tuition) as total')); 
    }])->paginate(10);

It's not working,I tried print the sql, and the sql:

select count(id) as numbers, sum(tuition) as total from `group` where `student`.`group_id` in (`1`, `2`, `4`, `5`, `6`, `7`, `8`, `11`, `12`, `13`, `14`)

I can get results when run raw sql in mysql, but laravel doesn't return anything about count or sum.

Answer 1

Tested a lot;

When I use find get only one row.

Group::with(['student'=>function($query){
    $query->select(DB::raw(' group_id ,count(`id`) as number, sum(tuition) as total')); 
}])->find(1);

It worked.

The only thing I miss is I need select student.group_id,which means foreign key in hasMany relationship.

But when you want use paginate or get method fetch multiply rows.

You will only get a total result in your first model object with others are null.

        {
            "id": 1,
            "name":"first",
            "student": [
                {

                    "group_id": 1,
                    "number": 129,
                    "total": "38700.00"
                }
            ]
        },
        {
            "id": 2,
            "name":"second",
            "student": []
        },
        {
            "id": 3,
            "name":"third",
            "student": []
        },

Just add ->groupBy('group_id) and you will get what you want

 Group::with(['student'=>function($query){
    $query->select(DB::raw('id, class_id ,count(`id`) as numbers, sum(tuition) as total'))->groupBy('group_id'); 
}])->paginate(10);

Result:

 {
            "id": 1,
            "name":"first",
            "student": [
                {

                    "group_id": 1,
                    "number": 40,
                    "total": "12000.00"
                }
            ]
        },
        {
            "id": 2,
            "name":"second",
            "student": [
                {

                    "group_id": 2,
                    "number": 43,
                    "total": "12900.00"
                }

                       ]
        },
        {
            "id": 3,
            "name":"third",
            "student": [
               {

                    "group_id": 3,
                    "number": 46,
                    "total": "13800.00"
                }
             ]
        },

Answer 2

You can use withCount() instead of with()

https://laravel.com/docs/5.5/eloquent-relationships#counting-related-models

Answer 3

Use withCount() instead of with():

Group::withCount([
    'student as numbers',
    'student as total' => function($query) {
        $query->select(DB::raw('sum(tuition)'));
    }
])->paginate(10);

Solution for Laravel 5.2:

Group::selectRaw('(select count(*) from students where groups.id = students.group_id) as numbers')
    ->selectRaw('(select sum(tuition) from students where groups.id = students.group_id) as total')
    ->paginate(10);