laravel - laravel 使用带有关系的闭包
问题描述
假设我有一个Group
和Student
模型,它是一对多的关系;
Group
有很多Student
,每个Student
都有id
和tuition
属性。
所以我想得到带有学生人数和所有学费的小组。
这是我的代码:
Group::with(['student'=>function($query){
$query->select(DB::raw('count(`id`) as numbers, sum(tuition) as total'));
}])->paginate(10);
它不起作用,我尝试打印 sql 和 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`)
在 mysql 中运行原始 sql 时我可以得到结果,但是 laravel 不返回任何关于count
or的内容sum
。
解决方案
使用withCount()
代替with()
:
Group::withCount([
'student as numbers',
'student as total' => function($query) {
$query->select(DB::raw('sum(tuition)'));
}
])->paginate(10);
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);