CODEWITHSUNDEEP
laraveleloquent
user1192474
user1192474

Reputation: 17

Order by highest hasmany

I have two tables Parts has many Offers.

I need to get all Parts sorted by Offers highest price or quantity, where offers price > 0 and offers quantity > 0.

Price and quantity are calculated fields.

How to achieve this?

I tried

Part::
addSelect([
  'quantity' => Offer::select('quantity')
    ->orderBy('quantity', 'desc')
    ->whereColumn('part_id', 'parts.id')
    ->limit(1)
])
->orderBy('quantity', 'desc')
->paginate(3)

But this way i cannot filter parts by quantity > 0 and price > 0

Also since quantity is calculated field when i do this 

->addSelect([
    'qty' => Offer::select(['quantity * 2 as qty'])
        ->orderBy('qty', 'asc')
        ->whereColumn('part_id', 'parts.id')
        ->limit(1)
])

I get Reference 'qty' not supported error

More explanation:

Each Part has many Offers. Each Offer has many Shippings. Offer`s 

available_quantity = quantity - sum of shipped quantities

I need to show Parts where available_quantity > 0 and sort Parts by available_quantity

Upvotes: 0

Views: 68

Answers (1)

TsaiKoga
TsaiKoga

Reputation: 13394

You can order it like this:

$shipping = Shipping::groupBy('offer_id')->select(DB::raw('SUM(quantity) AS sum_quantity'), 'offer_id', 'id');

Part::join('offers', 'offers.part_id', '=', 'parts.id')
    ->join(DB::raw("({$shipping->toSql()}) AS ship"), 'ship.offer_id', '=', 'offers.id')
    ->select('parts.*',DB::raw('MAX(offers.quantity - ship.sum_quantity) AS avaliable_quantity'))
    ->groupBy('part.id')
    ->orderBy('avaliable_quantity', 'desc')
    ->paginate(3)

Upvotes: 1

Related Questions