Ad

TYPO3 DBAL Querybuilder: Nested SELECT Statements?

- 1 answer

Is it possible to build nested SELECT statements like the one below using the DBAL QueryBuilder?

SELECT i.id, i.stable_id, i.version, i.title
FROM initiatives AS i
INNER JOIN (
    SELECT stable_id, MAX(version) AS max_version FROM initiatives GROUP BY stable_id
) AS tbl1
ON i.stable_id = tbl1.stable_id AND i.version = tbl1.max_version
ORDER BY i.stable_id ASC

The goal is to query an external non TYPO3 table which contains different versions of each data set. Only the data set with the highest version number should be rendered. The database looks like this:

id, stable_id, version, [rest of the data row]

stable_id is the external id of the data set. id is the internal autoincrement id. And version is also incremented automatically.

Code example:

$queryBuilder = GeneralUtility::makeInstance(ConnectionPool::class)->getQueryBuilderForTable($this->table);
$result = $queryBuilder
    ->select(...$this->select)
    ->from($this->table)
    ->join(
        'initiatives',
        $queryBuilder
            ->select('stable_id, MAX(version) AS max_version' )
            ->from('initiatives')
            ->groupBy('stable_id'),
        'tbl1',
        $queryBuilder->and(
            $queryBuilder->expr()->eq(
                'initiatives.stable_id',
                $queryBuilder->quoteIdentifier('tbl1.stable_id')
            ),
            $queryBuilder->expr()->eq(
                'initiatives.version',
                $queryBuilder->quoteIdentifier('tbl1.max_version')
            )
        )
    )
    ->orderBy('stable_id', 'DESC')

I cannot figure out the correct syntax for the ON ... AND statement. Any idea?

Ad

Answer

Short answer: it is not possible because the table to be joined in is generated on the fly. The related expression is back-ticked and thus causes an SQL error.

But: The SQL query can be changed to the following SQL query which does basically the same:

SELECT i1.id,stable_id, version, title, p.name, l.name, s.name
FROM initiatives i1
WHERE version = (
    SELECT MAX(i2.version)
    FROM initiatives i2
    WHERE i1.stable_id = i2.stable_id
)
ORDER BY stable_id ASC

And this can be rebuild with the DBAL queryBuilder:

$queryBuilder = GeneralUtility::makeInstance(ConnectionPool::class)->getQueryBuilderForTable($this->table);
$result = $queryBuilder
    ->select(...$this->select)
    ->from($this->table)
    ->where(
        $queryBuilder->expr()->eq(
            'initiatives.version',
            '(SELECT MAX(i2.version) FROM initiatives i2 WHERE initiatives.stable_id = i2.stable_id)'
        ),
    ->orderBy('stable_id', 'DESC')
    ->setMaxResults( 50 )
    ->execute();
Ad
source: stackoverflow.com
Ad