Feature Request: Add subquery support

[mrakolice]

Example of usage:

$repository->select()->subquery([
    'alias' => function(QueryBuilder $qb){
        $qb->from($tableName)->columns('field1', 'field2');
    }
]);

$repository->select()->subquery(function(QueryBuilder $qb){
        $qb->from($tableName)->columns('field1', 'field2');
    });

$repository->select()->subquery(function(QueryBuilder $qb) use($anotherSelect){
    $qb->addSelect($anotherSelect);
});

$repository->select()->with([
    'load' => function($qb){
        $qb->subquery(<...>);
    }
]);

Need to be done:

• Add possibility for generating subqueries in core
• Sync column aliases from subquery or parent query

[wolfy-j]

Add possibility for generating subqueries in core Already exist in DBAL.

Sync column aliases from subquery or parent query Already exists in DBAL.

Need to create new fetch method though.

[mrakolice]

If you provide some information about it, I'll do merge request tomorrow.

[wolfy-j]

Honestly, that's something for me personally. Take a look at Select/Loader functionality first.

[wolfy-j]

Can you give examples of sql produced? I can help with pin-pointing the location.

[mrakolice]

$repository->select()->subquery([
    'myAwesomeAlias' => function(QueryBuilder $qb){
        $qb->from($tableName)->columns('field1', 'field2');
    }
]);

SELECT myAwesomeAlias.field1, myAwesomeAlias.field2, related.*
FROM (
SELECT table1.field1, table1.field2
FROM table1
WHERE table1.field3 = 'value'
LIMIT 10 OFFSET 10
) as myAwesomeAlias
LEFT JOIN related ON related.table1_id = some_alias.field1

$repository->select()->subquery(function(QueryBuilder $qb){
        $qb->from($tableName)->columns('field1', 'field2')->
                  where(['field3'=>'value'])->limit(10)->offset(10);
    })->load('related', ['method'=>LEFT_JOIN, 'load'=>...]);

SELECT some_alias.field1, some_alias.field2, related.*
FROM (
SELECT table1.field1, table1.field2
FROM table1
WHERE table1.field3 = 'value'
LIMIT 10 OFFSET 10
) as some_alias
LEFT JOIN related ON related.table1_id = some_alias.field1

If not existed $qb->from($tableName), then by default $tableName is current tableName in select.

$anotherSelect = $table3Repository->select()->columns(['field1' => 'some_field1', 'field2' => 'some_field2']);

$table1Repository->select()->subquery(function(QueryBuilder $qb) use($anotherSelect){
    $qb->addSelect($anotherSelect);
});

SELECT some_alias.field1, some_alias.field2
FROM (
SELECT table3.some_field1 AS field1, table3.some_field2 AS field2
FROM table3
) as some_alias

But this select is going to resolve table1Entity fields. This behaviour can be useful for materialized views, I think, or something like that. Or just for refactoring/customization purposes.

[wolfy-j]

Try $select->from($insertQueryHere). How close is that to what you want, if it is we will make better syntax for it. Shouldn't be hard.

[mrakolice]

Yeah, this is some hack issue for me, but it's works. Problem with this solution - you need to interpolate the query and manually resolve table aliases.

$select = $orm->getRepository()->select();

$params = new QueryParameters();
$sqlStatement = $select->buildQuery()->sqlStatement($params);

$sqlStatement = Interpolator::interpolate($sqlStatement, $params->getParameters());

$resultStatement = preg_replace('/\sAS\s\"c\d+\"/', '', $sqlStatement);

$select = $select->from(
    (new Fragment('(' . $resultStatement . ') as "' .
                $select->getBuilder()->getLoader()->getAlias() . '"'))
);

[wolfy-j]

Good, so this is the baseline.