时间戳

首页 > 解决方案 > 将原始查询添加到雄辩的关系

php - 将原始查询添加到雄辩的关系

问题描述

我正在开发一个应用程序，Laravel 5.6我有一个包含以下列的简单表格：

company_id    project_id    company_role_id    company_specialisation_id

这表示模型AssociateCompanies，它的关系为company, project, role，specialisation现在我有一些查询来获取属性：

$companies = AssociateCompany::whereHas('company', function ($q) use ($request) {
    $q->whereHas('projectOwners', function ($q) use($request) {
        $q->where('slug', $request->slug);
    });
})->groupBy('company_id', 'company_specialisation_id')->with('company', 'role', 'specialisation');

我想从两列company_id和收集所有唯一字段及其计数specialisation_id，但groupBy没有给我正确的结果，所以我无法继续进行：

SQLSTATE [42000]：语法错误或访问冲突：1055 SELECT 列表的表达式 #1 不在 GROUP BY 子句中，并且包含在功能上不依赖于 GROUP BY 子句中的列的非聚合列“conxn.project_associate_company.id”；这与 sql_mode=only_full_group_by (SQL: select * from project_associate_companywhere exists (select , (select count( ) from project_associate_companywhere companies. id= project_associate_company. company_idand project_associate_company. deleted_atis null) as associated_projects_countfrom companieswhere project_associate_company. company_id= companies. idand exists (select * from projectsinner join project_owner_relationon projects. id= project_owner_relation.project_id其中companies. id= project_owner_relation.company_id和slug= lodha-patel-estate-tower-ab-mumbai 和projects. deleted_at为空）和companies。deleted_at为空）和project_associate_company。deleted_at是空组，由company_id, company_specialisation_id)"

所以我尝试运行这样的原始查询：

$companies = AssociateCompany::whereHas('company', function ($q) use ($request) {
        $q->whereHas('projectOwners', function ($q) use($request) {
            $q->where('slug', $request->slug);
        });
    })->selectRaw(DB::raw('COUNT(*) AS count GROUP BY company_id , company_specialisation_id'))
    ->with('company', 'companyRole', 'specialisation')->get();

SQLSTATE[42000]：语法错误或访问冲突：1064 您的 SQL 语法有错误；检查与您的 MySQL 服务器版本相对应的手册，以project_associate_company在第 1 行的 'GROUP BY company_id , company_specialisation_id from ' 附近使用正确的语法（SQL：select COUNT(*) AS count GROUP BY company_id , company_specialisation_id from project_associate_companywhere exists (select , ( select count( ) from project_associate_companywhere companies. id= project_associate_company. company_idand project_associate_company. deleted_atis null) as associated_projects_countfrom companieswhere project_associate_company. company_id= companies. idand exists (select * from projectsinner join project_owner_relationon projects. id= project_owner_relation. project_idwhere companies. id= project_owner_relation.company_id和slug= lodha-patel-estate-tower-ab-mumbai 和projects. deleted_at为空）和companies。deleted_at为空）和project_associate_company。deleted_at一片空白）”

建议我更好的方法来获得这个。谢谢。

标签： phpmysqllaraveleloquentlaravel-5.6

解决方案

最初我想注意你不能在“select”语句中“分组”。

所以你不能聚合非聚合列。这意味着分组字段可以有多个“角色”，因此您不能“加载”“角色”。查询应类似于以下之一：

AssociateCompany::whereHas('company', function ($q) use ($request) {
    $q->whereHas('projectOwners', function ($q) use($request) {
        $q->where('slug', $request->slug);
    });
})->select('company_id', 'company_specialisation_id', \DB::raw('COUNT(*) as cnt'))
->groupBy('company_id', 'company_specialisation_id')
->with('company', 'specialisation');

或者 ：

AssociateCompany::whereHas('company', function ($q) use ($request) {
    $q->whereHas('projectOwners', function ($q) use($request) {
        $q->where('slug', $request->slug);
    });
})->select('company_id', 'company_specialisation_id', 'company_role_id', \DB::raw('COUNT(*) as cnt'))
->groupBy('company_id', 'company_specialisation_id', 'company_role_id')
->with('company', 'specialisation', 'role');

我的建议是这样的，但我认为您可以使用原始 MySQL 查询来解决它，而不是使用 Eloquent。

推荐阅读