subQuery()

Creates a new subquery instance for building nested SQL expressions. You can use subqueries in WHERE, JOIN, or SELECT clauses. If you provide a host, the subquery will automatically get an alias.

Signature:

public function subQuery(?string $host = null): self

Example 1: Use subquery in WHERE IN

// Build subquery first
$sub = $db->subQuery();
$sub->whereDate('order_date', $db->interval('-7 DAY'), '>=');
$sub->groupBy('user_id');
$sub->get('test_orders', null, 'user_id');

// Use subquery in main query
$db->where('id', $sub, 'IN');
$db->get('test_users');
Note:
You must call get() on the subquery to generate its SQL, just like a regular query.

Example 2: JOIN with subquery and alias

// Subquery with alias
$sub = $db->subQuery('x');
$sub->groupBy('user_id');
$sub->get('test_orders', null, 'user_id, MAX(order_date) AS last_order_date');

// Join subquery
$db->join($sub, 'x.user_id = u.id');
$db->get('test_users u');
Alias Hint:
By calling subQuery('x'), the subquery will later appear as (...) AS x and must be referenced with that alias in JOINs or SELECTs.

Important Notes

  • Subqueries are full PDOdb instances – you can chain all methods as usual.
  • There is no reset() required – subqueries are isolated.
  • After you call get(...) on the subquery, you can use it as value or table reference.

See Also