Add support for inner Selects in SQL generation for joined entities

[mrakolice]

Example of generated SQL:

SELECT table1_alias.* FROM
(
    SELECT id, field1,  field2 from table1
    WHERE field1=%val1
    LIMIT 10 OFFSET 20
) as table1_alias
LEFT JOIN (
SELECT field3, field4, table1_id FROM table2
WHERE field3=%val2
LIMIT 10 OFFSET 20
) as table2_alias ON table1_alias.id = table2_alias.table1_id

[wolfy-j]

This is already possible for FROM part (though API is not publicly announced) , for the JOIN part see #23

[mrakolice]

Is this possible when you are working with loaders, or I should use only select()->buildQuery() notation?

[mrakolice]

Can you post some usages of this API or where I can find it in source code?

[mrakolice]

$select = TestModel::orm()->select()->limit(10)->offset(4);

TestModel::orm()->select()->from((new Fragment('(' . $select->sqlStatement() . ') as testModel'));

I'm finally find this solution.

[wolfy-j]

Yes, this will work :) Later update is require for JOINs.

[mrakolice]

TestModel::orm() ~ (new Orm())->getRepository(TestModel::class)

$sqlStatement = TestModel::orm()->select()->limit(10)->offset(0)->sqlStatement();
 
$resultStatement = preg_replace('/\sAS\s\"c\d+\"/', '', $sqlStatement);
$from = TestModel::orm()->select()->from(
        (new Fragment('(' . $resultStatement . ') as "' .
            TestModel::orm()->select()->getBuilder()->getLoader()->getAlias() . '"'))
);

I'll leaving this for future generations.