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.

Share this article

Posted

in

by

Tags: