CODEWITHSUNDEEP
laravel
anderlaini
anderlaini

Reputation: 1831

How to do LEFT OUTER JOIN in Laravel raw expression

This raw query is working well with two commented lines.

$q = DB::table('stock_items')
          ->selectRaw('stock_parts.title')
          ->selectRaw('COUNT(*) as qtyAvailable')
          ->selectRaw('SUM(shipments.item_cost) as totalValue')
          //->selectRaw('stock_alerts.minimum AS minimum')
          ->join('stock_parts', 'stock_items.stock_part_id', '=', 'stock_parts.id')
          ->join('shipments', 'shipments.id', '=', 'stock_items.shipment_id')
          //->leftJoin('stock_alerts', 'stock_alerts.stock_part_id', '=', 'stock_items.stock_part_id')
          ->whereNull('stock_items.status')
          ->where('stock_items.current_stock_id', '=', $stockId)
          ->groupBy('stock_parts.id')
          ->get();

The commented lines are needed to get information from another table.

In raw SQL I was using LEFT OUTER JOIN and it works.

Uncommenting those lines it shows this error:

SQLSTATE[42000]: Syntax error or access violation: 1055
Expression #4 of SELECT list is not in GROUP BY clause
and contains nonaggregated column 'rdphone-dev.stock_alerts.minimum'
which is not functionally dependent on columns in GROUP BY clause;
this is incompatible with sql_mode=only_full_group_by (SQL: select stock_parts.title, COUNT(*) as qtyAvailable, SUM(shipments.item_cost) as totalValue, stock_alerts.minimum AS minimum from `stock_items` inner join `stock_parts` on `stock_items`.`stock_part_id` = `stock_parts`.`id` inner join `shipments` on `shipments`.`id` = `stock_items`.`shipment_id` left join `stock_alerts` on `stock_alerts`.`stock_part_id` = `stock_items`.`stock_part_id` where `stock_items`.`status` is null and `stock_items`.`current_stock_id` = 1 group by `stock_parts`.`id`)",

What is the correct way to do LEFT OUTER JOIN in Laravel 5.6?

Upvotes: 0

Views: 928

Answers (1)

MaartenDev
MaartenDev

Reputation: 5811

Every field used in the select has to be included in the group by, I added the minimum column in the groupBy.

$q = DB::table('stock_items')
          ->selectRaw('stock_parts.title')
          ->selectRaw('COUNT(*) as qtyAvailable')
          ->selectRaw('SUM(shipments.item_cost) as totalValue')
          ->selectRaw('stock_alerts.minimum AS minimum')
          ->join('stock_parts', 'stock_items.stock_part_id', '=', 'stock_parts.id')
          ->join('shipments', 'shipments.id', '=', 'stock_items.shipment_id')
          ->leftJoin('stock_alerts', 'stock_alerts.stock_part_id', '=', 'stock_items.stock_part_id')
          ->whereNull('stock_items.status')
          ->where('stock_items.current_stock_id', '=', $stockId)
          ->groupBy('stock_parts.id', 'minimum')
          ->get();

Upvotes: 1

Related Questions