Complex Eloquent Query
A query below selects products that need to be updated in a remote application. It takes quantities of products in host application that are connected to source products application. On top of that it looks at all the orders that are in “Pending” status and reserves quantities for those products.
SELECT products.quantity_available, connector_products.stock_id, products.id, connector_products.sku, connector_products.connector_product_id, (SELECT SUM(order_items.quantity) FROM order_items LEFT JOIN orders ON order_items.order_id = orders.id WHERE orders.order_status = 'Pending' AND orders.is_archived = FALSE AND orders.account_id = connector_products.account_id AND orders.order_source = connector_products.connector_id AND order_items.sku = connector_products.sku AND (order_items.location <=> connector_products.warehouse_location GROUP BY order_items.sku) AS quantity_reserved FROM products LEFT JOIN connector_products ON products.id = connector_products.product_id WHERE connector_products.account_id = 1234 AND connector_products.connector_id = 1 AND products.bundled = FALSE ORDER BY products.id |
$reservedQuantity = \DB::table('order_items') ->leftJoin('orders', 'order_items.order_id', '=', 'orders.id') ->selectRaw('SUM(order_items.quantity)') ->where('orders.order_status', 'Pending') ->where('orders.is_archived', false) ->whereRaw('orders.account_id = connector_products.account_id') ->whereRaw('orders.order_source = connector_products.connector_id') ->whereRaw('order_items.sku = connector_products.sku') ->whereRaw('order_items.location <=> connector_products.warehouse_location') ->groupBy('order_items.sku'); $products = \DB::table('products') ->leftJoin('connector_products', 'products.id', '=', 'connector_products.product_id') ->select('products.quantity_available', 'connector_products.stock_id', 'products.id', 'connector_products.sku', 'connector_products.connector_product_id') ->selectSub($reservedQuantity, 'quantity_reserved') ->where('connector_products.account_id', $this->accountId) ->where('connector_products.connector_id', $this->globalConnectorId) ->where('products.bundled', false) ->orderBy('products.id') ->get(); |
First we create a sub-query to get reserved quantities. whereRaw is used to filter using columns. A <=> (sql null safe operator) is used because not all remote application may support concept of warehouses (locations). Therefore a match on null (null = null) should return true instead of null. The sub-query is then injected into products query using selectSub and aliased as quantity_reserved.
