I recently ran into a problem with my website for which the solution was a nested query (sometimes termed a subquery). However, I use Doctrine DBAL for creating my dynamic queries, and there's no documentation on how to do them.

The problem

For my art gallery, the raw SQL looks something like the following:

SELECT
    p.filename,
    p.description,
    p.created,
    array_agg(DISTINCT t.tag) as tags
FROM
    photos p
LEFT JOIN tags t ON p.filename = t.content_id
WHERE
    p.filename = :filename
    AND t.content_type = :content_type
GROUP BY
    p.filename

I started noticing an odd issue where, post insertion of an image into my gallery, I'd get no error, but the form would not appear to have processed. On further inspection, using Z-Ray from ZendHQ, I realized that the insertion was successful, but that the redirect to view the inserted image was returning a 404. I grabbed the executed SQL from Z-Ray for retrieving the image, and it returned no rows.

I started thinking about why this image wasn't posting, when others were, and realized there was one trivial difference: I wasn't including any hashtags in my description, which meant no tags.

Hopefully you can see where this is leading.

A LEFT JOIN will cause the entire query to fail if it finds no matching rows on the joined table.

Sure enough, when I removed the LEFT JOIN and the array_agg column, I got a hit.

The solution: nested query

The solution was to do a nested query, and to aggregate those results as an array. I'm using PostgreSQL, so the query looks like this:

SELECT
    p.filename,
    p.description,
    p.created,
    (SELECT ARRAY(
        SELECT DISTINCT tag FROM tags WHERE p.filename = content_id AND content_type = :content_type
        )) as tags
FROM
    photos p
WHERE
    p.filename = :filename
GROUP BY
    p.filename

With this approach, if no rows are returned from the tags table, an empty array is created; otherwise an array of the tag values that match is returned.

However, I didn't know how to create this query using the Doctrine DBAL query builder.

DBAL solution

When creating a SELECT using the DBAL query builder, you do something like this:

$select = $dbal->createQueryBuilder();
$select
    ->select(
        // one argument per column to select
    )
    ->from('table_name', 't') // alias the table

The arguments to select() are expected to be strings, and any given string can be an arbitrary SQL expression.

Creating the subselect is easy; you do it like any other query:

$tags = $dbal->createQueryBuilder();
$tags
    ->select('tag')
    ->distinct()
    ->from('tags')
    ->where('content_id = p.filename')
    ->andWhere('content_type = :content_type');

Now, how do I get that into a column string for a select?

The getSQL() method of a query builder will spit out the SQL sent. Moreover, it does not replace placeholders, so even if you set a bound parameter, it won't be injected into the generated SQL.

Knowing all this, I did the following:

$tags = $dbal->createQueryBuilder();
$tags
    ->select('tag')
    ->distinct()
    ->from('tags')
    ->where('content_id = p.filename')
    ->andWhere('content_type = :content_type');

$select = $dbal->createQueryBuilder();
$select
    ->select(
        'p.filename',
        'p.description',
        'p.created',
        sprintf('(SELECT ARRAY(%s)) as tags', $tags->getSQL()),
    )
    ->from('photos', 'p')
    ->where('p.filename = :filename')
    ->groupBy('p.filename')
    ->setParameter('filename', $filename, ParameterType::STRING)
    ->setParameter('content_type', 'photo', ParameterType::STRING);

(Where ParameterType is imported from the namespace Doctrine\DBAL.)

This approach worked immediately, and generated exactly the same result as the raw SQL I had tested.

Changelog

• 2025-03-07: clarified that the solution was targeting the DBAL query builder. DBAL can consume raw SQL as well, and does not require usage of the query builder.