Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
scopeBySaleType
public function scopeBySaleType($query, type)
{
$query->whereHas('sales', function ($query) use ($type) {
$query->where('id', function ($sub) {
$sub->from('sales')
->selectRaw('max(id)')
->whereColumn('sales.inventory_id', 'inventories.id');
})->where('type', $type);
});
}
@reinink

This comment has been minimized.

Copy link

@reinink reinink commented Oct 23, 2019

Macro to limit results by a sub query value:

Builder::macro('whereSubValue', function ($query, $operator = null, $value = null, $boolean = 'and') {
    list($query, $bindings) = $this->createSub($query);

    $this->addBinding($bindings, 'where')->where(DB::raw('('.$query.')'), $operator, $value, $boolean);
});

Using the macro:

InventoryItem::whereSubValue(
    Sale::select('type')
        ->whereColumn('inventory_item_id', 'inventory_items.id')
        ->orderBy('sold_at', 'desc')
        ->limit(1),
    'Retail'
)->get();

If you'd like to get the latest sale as a dynamic relationship as well:

InventoryItem::addSelect(['latest_sale_id' => Sale::select('id')
    ->whereColumn('inventory_item_id', 'inventory_items.id')
    ->where('type', 'Retail')
    ->orderBy('sold_at', 'desc')
    ->limit(1)
])->whereSubValue(
    Sale::select('type')
        ->whereColumn('inventory_item_id', 'inventory_items.id')
        ->orderBy('sold_at', 'desc')
        ->limit(1),
    'Retail'
)->get();
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment