Feature request: support for joining subqueries

[aimfeld]

Joining subqueries seems quite an important feature. However, as discussed here, DBAL does not support this currently. While DBAL looks great otherwise, for me this is a dealbreaker unfortunately. Is there any chance this could be implemented?

[deeky666]

@aimfeld what do you mean by DBAL does not support joining subqueries? Are you referring to DBAL's QueryBuilder class? If so, yes that is true, the builder does not have explicit API for such things and it won't in 2.x. DBAL's QueryBuilder is meant to be a simple SQL builder for SELECT, INSERT, UPDATE, DELETE that supports basic functionality such as joining, filtering, grouping and ordering. Also the builder is platform agnostic which is one reason why it can't support much more complex SQL, as SQL building differs from platform to platform. The only thing that is platform aware right now in the builder is the ORDER BY clause.
I am putting this to discussion for 3.0 though. If you need more complex SQL building, either write your own query builder, write the SQL "by hand" or try DQL if you are using ORM also.

[aimfeld]

@deeky666 Yes, I was referring to the DBAL's QueryBuilder class. Thanks for considering this for 3.0! I understand that this maybe out-of-scope if you want to keep the query builder simple and platform agnostic. As I need some advanced features, I think I'll use the Zend Framework 2 query builder instead of doctrine DBAL. For ORM, I'll use doctrine, as ORM features where dropped from ZF in version 2.

[deeky666]

You're welcome. Currently it looks like we most probably will have a lot more possibilities for 3.0 to achieve things like a complex query builder. So I'll leave the issue open for then.

[aimfeld]

After trying for some time, I found a solution to join subqueries with the DBAL QueryBuilder: http://stackoverflow.com/a/35656904/94289

[samuel4x4]

Hi @aimfeld , I've tried your example but it's not working. Is it working for you? What Doctrine version do you use? Thanks.

[aimfeld]

@samuel4x4 I have refactored this code since then to use doctrine DQL instead of DBAL. The DBAL solution worked for me using doctrine 2.5 DBAL

[samuel4x4]

@aimfeld Can you please show me how you did it, either with DQL or DBAL? Give me please an example that works. Thanks.

[morozov]

I use this quick and dirty solution in the projects where it's needed:

class QueryBuilder extends BaseQueryBuilder
{
    public function importSubQuery(BaseQueryBuilder $subBuilder)
    {
        $params = $subBuilder->getParameters();

        foreach ($params as $key => $value) {
            $this->createPositionalParameter(
                $value,
                $subBuilder->getParameterType($key)
            );
        }

        return $subBuilder->getSQL();
    }
}

It works when:

1. SELECTing from sub-queries:

   $inner = $conn->createQueryBuilder();
   $inner->select('id')
       ->from('users');
   
   $outer = $conn->createQueryBuilder();
   $outer->select('*')
       ->from('(' . $outer->importSubQuery($inner) . ')', 'q');

2. JOINing sub-queries:

   $outer = $conn->createQueryBuilder();
   $outer->select('*')
       ->from('users', 'u')
       ->join('q', '(' . $outer->importSubQuery($inner) . ')', 'u', 'q.id = u.id');

3. Using sub-queries in the IN clause:

   $outer = $conn->createQueryBuilder();
   $outer->select('*')
       ->from('users')
       ->where('id IN(' . $outer->importSubQuery($inner) . ')');

It works only with positional parameters, but named parameters are not portable anyways.

@deeky666 do you have any design requirements for this kind of feature?

[billschaller]

This is a big nasty can of worms.

[jnvsor]

It works only with positional parameters, but named parameters are not portable anyways.

DBAL internally changes named params to positional ones so there's no reason to use positional ones except convenience.

In fact, I'd say in QueryBuilder they should be removed entirely or deprecated and alias createPositionalParameter to createNamedParameter since you're very likely to assign them out of order.

---

Ideally the subquery would only be parsed when the main query is executed. This would let you add a subquery and alter its contents after the fact.

When a QueryBuilder is added to an sqlPart it could be wrapped in a SubqueryExpression which would contain the query object and a prefix/postfix (So that it could represent IN($subquery), x = ($subquery), JOIN ($subquery) sub ON..., etc)

When the query is compiled in getSQL the SubqueryExpression should assign the child parameters to the parent query. The $placeholder in createNamedParameter should be randomized with uniqid (And optionally configurable) which would prevent duplicate parameter names.

This way both the parent and subquery can be changed after assignment and both can be individually executed. Additionally you can use the same subquery in multiple main queries.

But as reading this has probably let on - it's no small tweak. (Just imagine recursing query detection!)

In the meantime your best bet is to make your subquery parameters from the main query, and make your subquery all in one go:

// Note that we create the parameter on the main query
$subquery->where('y = '.$query->createNamedParameter($value));
$query->innerJoin('main', $subquery->getSQL(), 'sub', 'sub.x = main.y');