Nested queries in Laravels Query Builder

Love them, or hate them: query builders and ORM's are the workhorses of modern web frameworks. Laravel, my framework of choice, comes packaged with the Fluent query builder and an ORM called Eloquent. Both are tightly coupled within the framework, with the former being able to transparently hydrate the later.

The benefit of query builders over simply executing raw SQL is that you can abstract away the database engine almost entirely, for example swapping out MySQL for SQLite while using the same code. Unfortunately as is usually the case with benefits, there is a down side; not all database engines are built equal and so a query builder tends to abstract only the features that are available across all database engines that it supports. Because of this a query builder can quickly become a hindrance by adding unnecessary complexity when you attempt to do something even remotely complex such as the following example:

SELECT * FROM (
    SELECT
        `employees`.`id`,
        CONCAT(`employees`.`first_name`, ' ', `employees`.`last_name`) as `full_name`,
        `employees`.`phone_number`
        GROUP_CONCAT(`departments`.`name`) as `employees_departments`
    FROM `employees`
    INNER JOIN `employees_departments` ON `employees_departments`.`employee_id` = `employees`.`id`
    INNER JOIN `departments` ON `departments`.`id` = `employees_departments`.`department_id`
    WHERE
        `employees`.`deleted_at` IS NULL AND
        `employees`.`archived_at` IS NULL
    GROUP BY `employees`.`id`
) as `t`
WHERE `t`.`employees_departments` NOT LIKE '%fish%'

The above, while not being the most intricate example of MySQL that I could come up with, is sufficiently complex enough to make translating it to Laravels Fluent Query Builder impossible without some use of RAW query strings^[1] and some clever additional logic as shown below:

$employees = Employee::select(
    'employees.id',
    DB::raw("CONCAT(`employees`.`first_name`, ' ', `employees`.`last_name`) as `full_name`"),
    'employees.phone_number',
    DB::raw("GROUP_CONCAT(`departments`.`name`) as `employees_departments`")
)
->join('employees_departments', 'employees_departments.employee_id', '=', 'employees.id')
->join('departments', 'departments.id', '=', 'employees_departments.departments_id')
->whereNull('employees.deleted_at')
->whereNull('employees.archived_at')
->groupby('employees.id')
->get();

In the initial raw SQL we use GROUP_CONCAT to concatenate all the linked department names into a single list for each employee record, this means that we can not add employees_departments NOT LIKE '%fish%' to the above Fluent built query because upon executing it will simply exclude joined department records with a name like "fish" rather than removing employees records that belong to a department with a name like "fish" – to do that you do need the outer query.

I have asked about this on IRC and seen a handful of postings on various forums during Google searches^[2] to be told that it is not possible to execute nested queries using Laravels query builder – something I now know to be only half wrong^[3].

While it is true that you can't write nested queries directly within Laravels Fluent Query Builder, you can use Fluent to build the inner query and then grab the built SQL and bindings from the \Illuminate\Database\Query\Builder object and add a raw query wrapper around it before injecting it into Eloquent via the hydrateRaw method like so:

$employees = Employee::select(
    'employees.id',
    DB::raw("CONCAT(`employees`.`first_name`, ' ', `employees`.`last_name`) as `full_name`"),
    'employees.phone_number',
    DB::raw("GROUP_CONCAT(`departments`.`name`) as `employees_departments`")
)
->join('employees_departments', 'employees_departments.employee_id', '=', 'employees.id')
->join('departments', 'departments.id', '=', 'employees_departments.departments_id')
->whereNull('employees.deleted_at')
->whereNull('employees.archived_at')
->groupby('employees.id'); // Note the missing get()

/** @var \Illuminate\Database\Query\Builder $queryBuilderObj */
$queryBuilderObj      = $employees->getQuery();
$craftedSql           = $queryBuilderObj->toSql();
$craftedSqlParameters = $queryBuilderObj->getBindings();

$finalQuerySql        = "SELECT * FROM ( $craftedSql ) as `t`";
$employees            = Employees::hydrateRaw($finalQuerySql, $craftedSqlParameters);

/** @var \Illuminate\Support\Collection $employees */
$employees            = $employees->where('t.employees_departments', 'NOT LIKE', '%fish%') 
                      ->get();

The beauty in this method is that you can nest queries more than just one level deep if you need to, all the while using both Fluent and Eloquent. The downside is that you will tightly couple your code to one specific database engine (in this case MySQL) which may negatively impact testing: for example you may be using an in memory SQLite engine that doesn't support the full MySQL dialect and therefore renders the code un-testable.