Laravel Subquery: Display Items If Subquery Returns Available Quantity Top 11 Latest Posts

Assuming you have a Laravel model for items and another model for inventory, and each item can have multiple inventory records with a quantity representing the number of items in stock, you can use a subquery to retrieve only the items that have at least one inventory record with a non-zero quantity.

Here’s an example query using Laravel’s query builder:

php
$items = DB::table('items')
            ->whereExists(function ($query) {
                $query->select(DB::raw(1))
                      ->from('inventory')
                      ->whereRaw('inventory.item_id = items.id')
                      ->where('inventory.quantity', '>', 0);
            })
            ->get();

This query uses the whereExists method to check if there is at least one inventory record for each item with a quantity greater than 0. The subquery selects a constant value of 1 to avoid retrieving unnecessary data and filters the inventory records by item ID and quantity.

You can also use a subquery in a select statement to retrieve additional information from the inventory records, such as the total available quantity for each item:

php
$items = DB::table('items')
            ->select('items.*', DB::raw('(SELECT SUM(quantity) FROM inventory WHERE item_id = items.id) as available_quantity'))
            ->whereExists(function ($query) {
                $query->select(DB::raw(1))
                      ->from('inventory')
                      ->whereRaw('inventory.item_id = items.id')
                      ->where('inventory.quantity', '>', 0);
            })
            ->get();

This query calculates the total available quantity for each item using a subquery with SUM and displays it as a virtual column named available_quantity in the result set. You can then access this column using the object properties of each item in the collection.