代码之家 › 专栏 › 技术社区 › Evol Rof

使用关系闭包

eloquent laravel

Evol Rof · 技术社区 · 6 年前

假设我有一个 Group 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`)

count 或 sum

3 回复 | 直到 6 年前

Jonas Staudenmeir 6 年前

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

Thai Nguyen Hung 6 年前

你可以用 withCount() 而不是 with()

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

Evol Rof 6 年前

当我使用 find 只得到一行。

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

我唯一想念的是我需要选择 student.group_id foreign key 在里面 hasMany

但是当你想用的时候 paginate 或 get 方法获取多行。

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

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

只需添加 ->groupBy('group_id) 你会得到你想要的

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

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