Laravel Query Builder SUM with where clause

Question

I have the following relationship: Items has many Records (Records.item_id references Items.id).

I have this query working fine:

$items = Item::addSelect(['quantity_sum' => Record::selectRaw('sum(quantity) as total') ->whereColumn('item_id', 'items.id') ->groupBy('item_id') ]) ->get();

But I need to get just the Items where the sum of records.quantity is lower than 1. I have tried add ->where('quantity_sum', '1') but I get this error message:

SQLSTATE[42S22]: Column not found: 1054 Unknown column 'quantity_sum' in 'where clause' (SQL: select items.*, (select sum(quantity) as total from records where item_id = items.id group by item_id) as quantity_sum from items where quantity_sum = 1)

Why can't I use the quantity_sum alias?
How can I filter only the Items whose sum in the Records.quantity column is less than 1?

Mondini · Accepted Answer · 2020-04-14 17:44:52Z

2

You should use HAVING

Something like that:

->havingRaw('sum(quantity) > ?', [1])

Edit:

$items = Item::addSelect(['quantity_sum' => Record::selectRaw('sum(quantity) as total')->whereColumn('item_id', 'items.id')->groupBy('item_id')]) ->havingRaw('quantity_sum < ?', [1]) ->groupBy('items.id') ->get();

edited Apr 14, 2020 at 17:44

answered Apr 13, 2020 at 17:32

Mondini

6106 silver badges16 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Alexandre Strapacao G. Vianna Over a year ago

This tip was good! I got it by this way: ->havingRaw('quantity_sum < ?', [1]) ->groupBy('items.id')

Alexandre Strapacao G. Vianna Over a year ago

May you edit your answer to correspond the final code? $items = Item::addSelect(['quantity_sum' => Record::selectRaw('sum(quantity) as total') ->whereColumn('item_id', 'items.id') ->groupBy('item_id') ]) ->havingRaw('quantity_sum < ?', [1]) ->groupBy('items.id') ->get();

OMR · Accepted Answer · 2020-04-13 18:25:16Z

you should define the relation between Item and Record Models like this:

in Item Model :

public function records() { return $this->hasMany(Record::class,'item_id'); }

in Record Model:

public function item() { return $this->belongsTo(Item::class,'item_id'); }

and simply in your query use 'doesntHave' like this:

$itemsWithoutRecords= Item::doesntHave('records')->get();

and if you like 'count' way you could use 'withCount' like:

$itemsWithoutRecords= Item::withCount('records')->having('records_count','<',1)-> get();

notice: both ways needs correct relation between the models

first way: https://laravel.com/docs/7.x/eloquent-relationships#querying-relationship-absence

second way: https://laravel.com/docs/7.x/eloquent-relationships#counting-related-models

The relationship is built right. The count is not the point here. I want to filter according to the sum of a column of a nested table. First sum(records.quantity) as quantity_sum and than bring just the quantity_sum < 0.
why getting all result from db and then filtering, didn't you should filter in query? if you want this you could use: $items= Item::withCount('records')-> get()->where('records_count','<',$theNumberYouWant);

Collectives™ on Stack Overflow

Laravel Query Builder SUM with where clause

2 Answers 2

2 Comments

2 Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

2 Comments

Related