doctrine/dbal — pull request #170 by wimvds (2012-07-10 12:02:36+00:00)
Title: [#DBAL-172] QueryBuilder joins are omitted if the table alias is not present in the "from" clause

Apparently that fix has never been included, don't know why exactly. But there are use cases where you want to join multiple tables without selecting any data from the joined tables.

Ie. a query like this one :
SELECT COUNT(DISTINCT news.id) FROM cb_newspages news
INNER JOIN nodeversion nv ON nv.refId = news.id AND nv.refEntityname='Entity\\News'
INNER JOIN nodetranslation nt ON nv.nodetranslation = nt.id
INNER JOIN node n ON nt.node = n.id
WHERE nt.lang = 'nl' AND n.deleted != 1

could be written with the querybuilder (using this patch) as follows :

$querybuilder->select('COUNT(DISTINCT news.id)')
->from('cb_newspages', 'news')
->innerJoin('news', 'nodeversion', 'nv', 'nv.refId = news.id AND nv.refEntityname=\'Entity\\\\News\'')
->innerJoin('nv', 'nodetranslation', 'nt', 'nv.nodetranslation = nt.id')
->innerJoin('nt', 'node', 'n', 'nt.node = n.id')
->where('nt.lang = :lang AND n.deleted != 1')
->setParameter('lang', $locale);

When you use an alias that isn't chained or used in a from clause it will still trigger a QueryException (as before).

ie.
$querybuilder->select('COUNT(DISTINCT news.id)')
->from('cb_newspages', 'news')
->innerJoin('news', 'nodeversion', 'nv', 'nv.refId = news.id AND nv.refEntityname=\'Entity\\\\News\'')
->innerJoin('invalid', 'nodetranslation', 'nt', 'nv.nodetranslation = nt.id')
->innerJoin('nt', 'node', 'n', 'nt.node = n.id')
->where('nt.lang = :lang AND n.deleted != 1')
->setParameter('lang', $locale);

Will trigger the following QueryException :

"The given alias 'invalid' is not part of any FROM or JOIN clause table. The currently registered aliases are: news, nv, nt, n."