CODEWITHSUNDEEP
phpmysqllaraveleloquentlaravel-5.6
Nitish Kumar
Nitish Kumar

Reputation: 6276

Adding raw query to eloquent relationship

I am developing an application in Laravel 5.6 I'm having a simple table with following columns:

company_id    project_id    company_role_id    company_specialisation_id

And this represents the model AssociateCompanies, which has relation of company, project, role, specialisation now I am having some query to get the attribute:

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

I want to collect all unique fields with their counts from two columns company_id and specialisation_id, but groupBy is not giving me proper the results so I am unbale to proceed further:

SQLSTATE[42000]: Syntax error or access violation: 1055 Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'conxn.project_associate_company.id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by (SQL: select * from project_associate_company where exists (select , (select count() from project_associate_company where companies.id = project_associate_company.company_id and project_associate_company.deleted_at is null) as associated_projects_count from companies where project_associate_company.company_id = companies.id and exists (select * from projects inner join project_owner_relation on projects.id = project_owner_relation.project_id where companies.id = project_owner_relation.company_id and slug = lodha-patel-estate-tower-a-b-mumbai and projects.deleted_at is null) and companies.deleted_at is null) and project_associate_company.deleted_at is null group by company_id, company_specialisation_id)"

So I tried running raw queries like this:

$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]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'GROUP BY company_id , company_specialisation_id from project_associate_company' at line 1 (SQL: select COUNT(*) AS count GROUP BY company_id , company_specialisation_id from project_associate_company where exists (select , (select count() from project_associate_company where companies.id = project_associate_company.company_id and project_associate_company.deleted_at is null) as associated_projects_count from companies where project_associate_company.company_id = companies.id and exists (select * from projects inner join project_owner_relation on projects.id = project_owner_relation.project_id where companies.id = project_owner_relation.company_id and slug = lodha-patel-estate-tower-a-b-mumbai and projects.deleted_at is null) and companies.deleted_at is null) and project_associate_company.deleted_at is null)"

Suggest me better way to get this. Thanks.

Upvotes: 0

Views: 525

Answers (1)

Mesuti
Mesuti

Reputation: 908

Initially I want to notice you can not "group by" inside the "select" statement.

So you can not aggregate the non-aggregated columns. This means is grouped fields can be has got multiple "role" so you can not "load" the "roles". The query should be like the one of the follows:

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

Or :

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

My suggestions are like this but I think you can solve it with raw MySQL queries rather than use to Eloquent.

Upvotes: 1

Related Questions