Query Builder Examples¶

This page provides detailed examples for building queries with the CakePHP Query Builder in ZubZet.

Basic WHERE Conditions¶

Simple Equality¶

$query = $this->dbSelect(['id', 'name', 'email'], 'users')
              ->where(['status' => 'active']);

SELECT id, name, email
FROM users
WHERE status = 'active'

Multiple Conditions (AND)¶

By default, multiple conditions in where() are combined with AND:

$query = $this->dbSelect(['id', 'name'], 'users')
              ->where([
                  'status' => 'active',
                  'role' => 'admin'
              ]);

SELECT id, name
FROM users
WHERE status = 'active' 
AND role = 'admin'

Chained WHERE (AND)¶

You can also chain multiple where() calls:

$query = $this->dbSelect(['id', 'name'], 'users')
              ->where(['status' => 'active'])
              ->where(['role' => 'admin']);

SELECT id, name
FROM users
WHERE status = 'active'
AND role = 'admin'

OR Conditions¶

Using OR¶

$query = $this->dbSelect(['id', 'name'], 'users')
              ->where([
                  'OR' => [
                      'role' => 'admin',
                      'id' => 1
                  ]
              ]);

SELECT id, name
FROM users
WHERE (
    role = 'admin' OR
    id = 1
)

Combined AND and OR¶

$query = $this->dbSelect(['id', 'name'], 'users')
              ->where([
                  'status' => 'active',
                  'OR' => [
                      'role' => 'admin',
                      'id' => 1
                  ]
              ]);

SELECT id, name
FROM users
WHERE (
    status = 'active' AND (
        role = 'admin' OR
        id = 1
        )
    )

IN and NOT IN¶

WHERE IN List¶

$query = $this->dbSelect(['id', 'name'], 'users')
              ->whereInList('id', [1, 2, 3, 4, 5]);

SELECT id, name
FROM users
WHERE id in (1, 2, 3, 4, 5)

WHERE NOT IN List¶

$query = $this->dbSelect(['id', 'name'], 'users')
              ->whereNotInList('id', [1, 2, 3, 4, 5]);

SELECT id, name
FROM users
WHERE id not in (1, 2, 3, 4, 5)

Comparison Operators (Expressions)¶

Greater Than (>)¶

$query = $this->dbSelect(['id', 'name', 'age'], 'users')
              ->where(['age >' => 18]);

SELECT id, name, age
FROM users
WHERE age > 18

Greater Than or Equal (>=)¶

$query = $this->dbSelect(['id', 'name', 'age'], 'users')
              ->where(['age >=' => 21]);

SELECT id, name, age
FROM users
WHERE age >= 21

Less Than (<)¶

$query = $this->dbSelect(['id', 'name', 'price'], 'products')
              ->where(['price <' => 100]);

SELECT id, name, price
FROM products
WHERE price < 100

Not Equal (!=)¶

$query = $this->dbSelect(['id', 'name'], 'users')
              ->where(['status !=' => 'deleted']);

SELECT id, name
FROM users
WHERE status != 'deleted'

BETWEEN¶

$query = $this->dbSelect(['id', 'name', 'price'], 'products')
              ->where(['price >=' => 10, 'price <=' => 100]);

SELECT id, name, price
FROM products WHERE (
    price >= 10 AND
    price <= 100
)

LIKE Conditions¶

Simple LIKE¶

$query = $this->dbSelect(['id', 'name'], 'users')
              ->where(['name LIKE' => '%john%']);

SELECT id, name
FROM users
WHERE name like '%john%'

NOT LIKE¶

$query = $this->dbSelect(['id', 'name'], 'users')
              ->where(['name NOT LIKE' => '%test%']);

SELECT id, name
FROM users
WHERE name not like '%test%'

NULL Conditions¶

IS NULL¶

$query = $this->dbSelect(['id', 'name'], 'users')
              ->whereNull('deleted_at');

SELECT id, name
FROM users
WHERE (deleted_at) IS NULL

IS NOT NULL¶

$query = $this->dbSelect(['id', 'name'], 'users')
              ->whereNotNull('deleted_at');

SELECT id, name
FROM users
WHERE (deleted_at) IS NOT NULL

Column Aliases¶

Select with Aliases¶

$query = $this->dbSelect([
                  'id',
                  'user_name' => 'name',
                  'user_email' => 'email'
              ], 'users');

$result = $this->exec($query);

SELECT id,
    name AS user_name,
    email AS user_email
FROM users

Table Alias¶

$query = $this->dbSelect(['u.id', 'u.name'], ['u' => 'users'])
              ->where(['u.status' => 'active']);

SELECT u.id, u.name
FROM users u
WHERE u.status = 'active'

JOINs¶

INNER JOIN¶

$query = $this->dbSelect(['users.id', 'users.name', 'orders.total'], 'users')
              ->innerJoin('orders', ['users.id = orders.user_id']);

SELECT
    users.id,
    users.name,
    orders.total
FROM users
INNER JOIN orders orders
ON users.id = orders.user_id

LEFT JOIN¶

$query = $this->dbSelect(['users.id', 'users.name', 'orders.total'], 'users')
              ->leftJoin('orders', ['users.id = orders.user_id']);

SELECT
    users.id,
    users.name,
    orders.total
FROM users
LEFT JOIN orders orders
ON users.id = orders.user_id

RIGHT JOIN¶

$query = $this->dbSelect(['users.id', 'users.name', 'orders.total'], 'users')
              ->rightJoin('orders', ['users.id = orders.user_id']);

SELECT
    users.id,
    users.name,
    orders.total
FROM users
RIGHT JOIN orders orders
ON users.id = orders.user_id

JOIN with Table Aliases¶

$query = $this->dbSelect(['u.id', 'u.name', 'o.total'], ['u' => 'users'])
              ->leftJoin(['o' => 'orders'], ['u.id = o.user_id']);

SELECT u.id, u.name, o.total
FROM users u
LEFT JOIN orders o
ON u.id = o.user_id

Multiple JOINs¶

$query = $this->dbSelect([
                  'u.id', 
                  'u.name', 
                  'o.total', 
                  'p.name'
              ], ['u' => 'users'])
              ->leftJoin(['o' => 'orders'], ['u.id = o.user_id'])
              ->leftJoin(['p' => 'products'], ['o.product_id = p.id']);

SELECT u.id, u.name, o.total, p.name
FROM users u
LEFT JOIN orders o
ON u.id = o.user_id
LEFT JOIN products p
ON o.product_id = p.id

JOIN with Additional Conditions¶

$query = $this->dbSelect(['u.id', 'u.name', 'o.total'], ['u' => 'users'])
              ->leftJoin(['o' => 'orders'], ['u.id = o.user_id', 'o.status' => 'completed']);

SELECT u.id, u.name, o.total
FROM users u
LEFT JOIN orders o
ON (
    u.id = o.user_id AND
    o.status = 'completed'
)

ORDER BY¶

Simple Order¶

$query = $this->dbSelect(['id', 'name'], 'users')
              ->orderAsc("name");

SELECT id, name
FROM users
ORDER BY name ASC

Multiple Order Columns¶

 $query = $this->dbSelect(['id', 'name', 'created'], 'users')
                ->orderDesc("created")
                ->orderAsc("name");

SELECT id, name, created
FROM users
ORDER BY created DESC, name ASC

LIMIT and OFFSET¶

Limit Results¶

$query = $this->dbSelect(['id', 'name'], 'users')
              ->limit(10);

SELECT id, name
FROM users
LIMIT 10

Limit with Offset (Pagination)¶

$query = $this->dbSelect(['id', 'name'], 'users')
              ->limit(10)
              ->offset(20);

SELECT id, name
FROM users
LIMIT 10
OFFSET 20

GROUP BY and HAVING¶

Simple GROUP BY¶

$query = $this->dbSelect(['status', 'COUNT(*) AS count'], 'users')
              ->groupBy(['status']);

SELECT status, COUNT(*) AS count
FROM users 
GROUP BY status

GROUP BY with HAVING¶

$query = $this->dbSelect(['status', 'COUNT(*) AS count'], 'users')
              ->groupBy(['status'])
              ->having(['COUNT(*) >' => 5]);

SELECT status, COUNT(*) AS count
FROM users
GROUP BY status
HAVING COUNT(*) > 5

DISTINCT¶

$query = $this->dbSelect(['status'], 'users')
              ->distinct();

SELECT DISTINCT status FROM users

Complex Query Example¶

Here's a complete example combining multiple features:

$query = $this->dbSelect([
                  'u.id',
                  'u.name',
                  'u.email',
                  'order_count' => 'COUNT(o.id)',
                  'total_spent' => 'SUM(o.total)'
              ], ['u' => 'users'])
              ->leftJoin(['o' => 'orders'], ['u.id = o.user_id'])
              ->where([
                  'u.status' => 'active',
                  'u.created >=' => '2025-01-01',
                  'o.status IN' => ['completed', 'shipped']
              ])
              ->group(['u.id', 'u.name', 'u.email'])
              ->having(['COUNT(o.id) >=' => 3])
              ->orderDesc('total_spent')
              ->limit(10);

$result = $this->exec($query);

SELECT
    u.id,
    u.name,
    u.email,
    COUNT(o.id) AS order_count,
    SUM(o.total) AS total_spent
FROM users u
LEFT JOIN orders o
ON u.id = o.user_id
WHERE (
    u.status = 'active' AND
    u.created >= '2025-01-01' AND
    o.status in ('completed','shipped')
)
GROUP BY u.id, u.name, u.email
HAVING COUNT(o.id) >= 3
ORDER BY total_spent DESC
LIMIT 10

More Information¶

For the full CakePHP Query Builder documentation, visit:
CakePHP Database Query Builder