Dfyjkt

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.

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);

You can use withCount() instead of with()

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

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"
 
 ]
 ,