jeudi 28 février 2019

Lumen API select data with group by aggreated

I'm currently using Lumen 5.7 for my back end API .I need to select some fields from multiple tables with using left join. I have the following query(Plain mysql query)

SELECT issue_id,issue_receipt_id, date_format(issue_datetime, '%d-%m-%Y') as issue_datetime,
issue_item,sum(issue_wt) as issue_wt, round(avg(issue_pure),3) as issue_pure,
sum(issue_fine) as issue_fine, sum(issue_cash) as issue_cash,
sum(receipt_wt) as receipt_wt, sum(receipt_fine) as receipt_fine,
pure_name as issue_item_name 
FROM item_issues 
LEFT JOIN receipt_items ON receipt_id = issue_receipt_id 
LEFT JOIN purities ON pure_id = issue_item
group by issue_receipt_id;

And I have tried in lumen as like,

$receipts = DB::table('item_issues')            
            ->leftJoin('receipt_items', 'receipt_items.receipt_id', '=', 'item_issues.issue_receipt_id')
            ->leftJoin('purities', 'purities.pure_id', '=', 'item_issues.issue_item')
            ->select('issue_id', 'issue_receipt_id', DB::raw("date_format(issue_datetime, '%d-%m-%Y') as issue_datetime"), 'issue_item', 'pure_name', DB::raw('sum(issue_wt) as issue_wt'), 
            DB::raw('round(avg(issue_pure),3) as issue_pure'),
            DB::raw('sum(issue_fine) as issue_fine'),
            DB::raw('sum(issue_cash) as issue_cash'),
            DB::raw('sum(receipt_wt) as receipt_wt'),
            DB::raw('sum(receipt_fine) as receipt_fine'))
            ->groupBy('item_issues.issue_receipt_id', 'item_issues.issue_item')->get();

But it producing error like Syntax error or access violation: 1055 Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'item_issues.issue_id' which is not functionally dependent on columns in GROUP BY clause; But I have to select the above fields, how to do this without the issue. And I have tried to disable strict mode but I don't know where to disable this option whether in .env or in some other file. I couldn't find the database.config in lumen. Any one can help on this.



via Chebli Mohamed

Aucun commentaire:

Enregistrer un commentaire