CODEWITHSUNDEEP
phpmysqldatabaselaravellaravel-5
Yudhistira
Yudhistira

Reputation: 43

SQLSTATE[42000]: Syntax error or access violation: 1055 Laravel 5.8 MySQL

I have MySQL Query (this is work) like :

SELECT 
    project_bills.*, 
    project_deliveryorder.totalcost,
    project_deliveryorder.deliveryorder_number,
    project_deliveryorder.deliveryorder_subject,
    SUM(project_bills_payment.payment_amount) AS TOTAL
FROM 
    project_bills 
JOIN
    project_deliveryorder ON project_bills.id_deliveryorder = project_deliveryorder.id
JOIN
    project_bills_payment ON project_bills.id = project_bills_payment.id_bill
GROUP BY
    project_bills.id
ORDER BY
    project_bills.bill_date, project_bills.id DESC;

and I tried to translate to Laravel format like :

DB::table('project_bills')
    ->join('project_deliveryorder', 'project_bills.id_deliveryorder', '=', 'project_deliveryorder.id')
    ->join('project_bills_payment', 'project_bills.id', '=', 'project_bills_payment.id_bill')
    ->select('project_bills.*', 'project_deliveryorder.totalcost', 'project_deliveryorder.deliveryorder_number', 'project_deliveryorder.deliveryorder_subject', DB::raw('SUM(project_bills_payment.payment_amount) AS TOTAL'))
    ->groupBy('project_bills.id')
    ->orderBy('project_bills.bill_date', 'DESC')
    ->orderBy('project_bills.id', 'DESC')
    ->get();

and then I got some error like :

SQLSTATE[42000]: Syntax error or access violation: 1055 'db_tcm.project_bills.id_deliveryorder' isn't in GROUP BY

I have read Group by not working - Laravel in Stack Overflow too, so what best way to fix this issue?

Upvotes: 1

Views: 1898

Answers (1)

Qirel
Qirel

Reputation: 26450

When you have a GROUP BY in your query, you can only select columns that are in the GROUP BY, or is an aggregate function like SUM(), MIN(), MAX() etc.

If I understand your data correctly, you can extract the SUM() in a separate query, where you JOIN it into the main query.

DB::table('project_bills as pb')
    ->join('project_deliveryorder as pdo', 'pb.id_deliveryorder', '=', 'pdo.id')
    ->join(DB::raw('(SELECT id_bill, SUM(payment_amount) AS TOTAL
                      FROM project_bills_payment
                      GROUP BY id_bill) AS pbp'), 'pb.id', '=', 'pbp.id_bill')
    ->select('pb.*', 
             'pdo.totalcost', 
             'pdo.deliveryorder_number', 
             'pdo.deliveryorder_subject', 
             'pbp.TOTAL')
    ->orderBy('pb.bill_date', 'DESC')
    ->orderBy('pb.id', 'DESC')
    ->get();

Disabling strict mode or disabling ONLY_FULL_GROUP_BY is in my opinion not a good idea, and if you can avoid it by reworking your query, that's usually much better.

Upvotes: 2

Related Questions