Sub Queries

Sub Queries

subQuery() builds a SELECT inside a closure that receives a fresh builder. The result is returned as a RawQuery, which you can drop into any context that accepts one — WHERE IN, FROM, JOIN, or standalone.

Signature

public function subQuery(
    Closure $closure,
    ?string $alias = null,
    bool $isIntervalQuery = true
): RawQuery

• $closure — receives a fresh QueryBuilder (cloned from the parent, with structure reset). Use the same fluent API to build the inner SELECT.
• $alias — optional alias. Only valid when $isIntervalQuery is true (otherwise the call throws).
• $isIntervalQuery — when true (default), the emitted SQL is wrapped in parentheses. Set to false for a stand-alone fragment.

In a WHERE IN

The most common use:

$qb->select('u.name')
   ->from('users AS u')
   ->whereIn('u.id', $qb->subQuery(function (QueryBuilder $sub) {
       $sub->select('id')
           ->from('roles')
           ->where('name', 'admin');
   }));

echo $qb->generateSelectQuery();

SELECT `u`.`name`
  FROM `users` AS `u`
 WHERE `u`.`id` IN (SELECT `id` FROM `roles` WHERE `name` = :name)

As a derived FROM table

$derived = $qb->subQuery(function (QueryBuilder $sub) {
    $sub->select('id', 'title', 'user_id')
        ->from('posts')
        ->where('user_id', 5);
}, 'p');

$qb->select('u.name', 'p.title')
   ->from('users AS u')
   ->join($derived, 'p.user_id = u.id', '');

echo $qb->generateSelectQuery();

SELECT `u`.`name`, `p`.`title`
  FROM `users` AS `u`
  JOIN (SELECT `id`, `title`, `user_id` FROM `posts` WHERE `user_id` = 5) AS `p`
    ON `p`.`user_id` = `u`.`id`
 WHERE 1

The alias goes on the sub-query call ('p'); the JOIN keyword can be left empty ('') for an unqualified JOIN, or set to 'INNER', 'LEFT', etc.

Standalone (no parentheses)

When you want the raw inner SELECT without the wrapping parentheses, pass $isIntervalQuery = false:

$raw = $qb->subQuery(function (QueryBuilder $sub) {
    $sub->select('id')->from('users')->where('active', 1);
}, null, false);

echo (string) $raw;
// SELECT `id` FROM `users` WHERE `active` = 1

Useful when you want to compose a larger SQL fragment by hand and slot a generated SELECT into it.

⚠️ Passing an alias and $isIntervalQuery = false raises To define alias to a subquery, it must be an inner query. Aliases only make sense on parenthesized fragments.

Inside where() directly

Any operator that accepts a RawQuery works:

$qb->from('users')
   ->where('id', '=', $qb->subQuery(function (QueryBuilder $sub) {
       $sub->select('user_id')
           ->from('latest_login')
           ->orderBy('logged_at', 'DESC')
           ->limit(1);
   }));
// WHERE `id` = (SELECT `user_id` FROM `latest_login` WHERE 1
//               ORDER BY `logged_at` DESC LIMIT 1)

Sub-query parameters

The closure receives a clone of the outer builder. The clone has its own structure and its own parameter bag, so sub-query parameters do not collide with the outer query's parameters.

$qb->select('u.name')
   ->from('users AS u')
   ->whereIn('u.role_id', $qb->subQuery(function (QueryBuilder $sub) {
       $sub->select('id')
           ->from('roles')
           ->whereIn('name', ['admin', 'moderator']);
   }));

SELECT `u`.`name`
  FROM `users` AS `u`
 WHERE `u`.`role_id` IN (
     SELECT `id` FROM `roles` WHERE `name` IN (:name, :name_1)
 )

The :name / :name_1 placeholders live in the inner builder's bag. The resulting SQL is captured as a RawQuery and embedded into the outer query, so the placeholders are already part of the final SQL string — but when you execute the outer query, those placeholders need to come from the outer parameter bag. The cleanest pattern: hoist sub-query values out as outer parameters when they're dynamic.

$qb->setParameter('admin_role', 'admin');
$qb->select('u.name')
   ->from('users AS u')
   ->whereIn('u.role_id', $qb->subQuery(function (QueryBuilder $sub) {
       $sub->select('id')->from('roles')->where('name', $sub->raw(':admin_role'));
   }));
// The placeholder :admin_role lives in the OUTER bag.

Multiple sub-queries

$qb->select('u.name')
   ->from('users AS u')
   ->whereIn('u.id', $qb->subQuery(fn (QueryBuilder $sub) =>
       $sub->select('user_id')->from('orders')->where('status', 'paid')
   ))
   ->andWhereNotIn('u.id', $qb->subQuery(fn (QueryBuilder $sub) =>
       $sub->select('user_id')->from('bans')
   ));

When to reach for RawQuery instead

RawQuery is the right tool when the sub-query is static:

$qb->whereIn('id', $qb->raw('(SELECT user_id FROM bans)'));

That skips the closure indirection. Reserve subQuery() for cases where the inner SELECT is itself dynamic. See Raw Queries for details.

---

Next: Grouped Conditions