Advanced Queries

Example 1

Get first 10 totals of orders by customer and month with more than 1 order and sort by the greater:

SQL:

SELECT
  customers.id, customers.name, EXTRACT(MONTH FROM orders.order_date) AS month, 
  COUNT(orders.id) AS qty, SUM(orders.total) AS total
FROM
  orders
INNER JOIN 
  customers ON customers.id = orders.customer_id
GROUP BY
  customers.id, customers.name, EXTRACT(MONTH FROM orders.order_date)
HAVING
  COUNT(orders.id) > 1
ORDER BY
  5 DESC, 3
LIMIT 10;

PHP:

$rows = $repository
    ->newQuery()
    ->select([
        'customers.id', 
        'customers.name', 
        'EXTRACT(MONTH FROM orders.order_date) AS month', 
        'COUNT(orders.id) AS qty', 
        'SUM(orders.total) AS total',
    ])
    ->innerJoin('customers', 'customers.id', 'orders.customer_id')
    ->groupBy([
        'customers.id', 
        'customers.name', 
        'EXTRACT(MONTH FROM orders.order_date)',
    ])
    ->having('COUNT(orders.id) > 1')
    ->orderBy('5 DESC', '3')
    ->limit(10)
    ->fetch();    

Example 2

Get all customers that doesn't request a single order:

SQL:

SELECT
  customers.id, customers.name, customers.email
FROM
  customers
LEFT JOIN 
  orders ON orders.customer_id = customers.id
WHERE
  orders.id IS NULL
ORDER BY
  customers.name;

PHP:

$rows = $repository
    ->newQuery()
    ->select([
        'customers.id', 
        'customers.name',
        'customers.email',
    ])
    ->leftJoin('orders', [
        'orders.customer_id' => 'customers.id',
    ])
    ->isNull('orders.id')
    ->orderBy('name')
    ->fetch();    

---

🡄 Caching | Debugable 🡆