Custom Queries in laravel 5

0
votes

How can i run this type of query in laravel 5.4

     select items.id,items.name,total,region from items LEFT OUTER JOIN
     (select  purchases.region_id as region , sum(purchases_detail.quantity) 
     as total,purchases_detail.item_id
     from purchases_detail
     left join purchases on(purchases.id=purchases_detail.purchase_id)
     GROUP BY purchases_detail.item_id) pd on (pd.item_id=items.id)

when I tried this in DB::select('') it gave me an error :

SQLSTATE[42000]: Syntax error or access violation: 1055 'amsdb.purchases.region_id' isn't in GROUP BY (SQL: select items.id,items.name,total,region from items LEFT OUTER JOIN (select purchases.region_id as region , sum(purchases_detail.quantity) as total,purchases_detail.item_id from purchases_detail left join purchases on(purchases.id=purchases_detail.purchase_id) GROUP BY purchases_detail.item_id) pd on (pd.item_id=items.id))

but it works well when I run it Phpmyadmin

2
mysqllaravelphpmyadminquery-builderlaravel-query-builder
You should turn off strict in Laravel and everything will work as it should. Go to config/database.php and in the mysql configuration array, change strict => true to strict => false ;) - Maraboc
@Maraboc Bad advice, he should fix his query. - Tim Biegeleisen
@TimBiegeleisen Ithink even if he fix the query he should update strict mode ! - Maraboc
thanks @Maraboc it worked for me but I want to learn that how can I run this type of query in Laravel Syntax. - Aihtsham Ali
@aihtshamali There is nothing extra you need to do in Laravel once you have turned off strict mode in MySQL. But you should really fix the logic of your query such that you don't need to do that. - Tim Biegeleisen

2 Answers

0
votes

As the error message tells you, the purchases.region_id column does not appear as an aggregate, nor are you grouping by it. The quick fix is to just remove this column from the inner and outer query:

SELECT
    items.id,
    items.name,
    pd.total
FROM items
LEFT JOIN
(
    SELECT
        t1.item_id,
        SUM(t1.quantity) AS total
     FROM purchases_detail t1
     LEFT JOIN purchases t2
         ON t2.id = t1.purchase_id
     GROUP BY t1.item_id
) pd
     ON pd.item_id = items.id
0
votes

Not sure, but I think that the select() method cannot contain all your SQL query.

I don't know if it will work, but try this one. It uses many query builder functions:

$subQuery = DB::table('purchases_detail')
    ->select(DB::raw('purchases.region_id as region , sum(purchases_detail.quantity) 
     as total,purchases_detail.item_id'))
    ->leftJoin('purchases', 'purchases.id', '=', 'purchases_detail.purchase_id')
    ->groupBy('purchases_detail.item_id')
    ->toSql();

$result = DB::table('items')
    ->select('items.id', 'items.name', 'total,region')
    ->leftJoin($subQuery . ' pd', 'pd.item_id', '=', 'items.id') // if not working, try ->leftJoin(DB::raw($subQuery) . ' pd', 'pd.item_id', '=', 'items.id')
    ->get();

Since your query has a nested SELECT, you have to make it in two times.