Feature #99415
closed
Missing QueryBuilder feature: CROSS JOIN (JOIN without ON)
0%
Description
In some complex query situations it can be necessary to cross join tables without ON clause, and only filter results in the WHERE (business logic) part of the query.
The QueryBuilder as it currently exists always requires an ON condition for joins. It claims, a basic join was by definition an INNER JOIN, ignoring the existence of CROSS JOIN.
See https://mariadb.com/kb/en/joining-tables-with-join-clauses/ for more.
The QueryBuilder does mention the following:
->from() can be called multiple times and will create the cartesian product of tables if not restricted by an according ->where() or ->andWhere() expression. In general, it is a good idea to use ->from() only once per query and model multi-table selection with an explicit ->join() instead.
If you disagree that a crossJoin method should be added, or that the join method should not require a conditional argument, then at least should the equivalence of multiple from() calls with a missing crossJoin() be mentioned in the documentation.
Updated by Leonie Philine almost 2 years ago
For anyone else having the same problem:
At least an unclean INNER JOIN with null as $condition and any existing alias as $fromAlias can be used. It's not clean, but it is better than nothing.
Why "unclean" you ask?
In MariaDB, CROSS JOIN is a syntactic equivalent to INNER JOIN (they can replace each other). In standard SQL, they are not equivalent. INNER JOIN is used with an ON clause, CROSS JOIN is used otherwise.
Source: https://mariadb.com/kb/en/join-syntax/ (emphasis mine)
Updated by Stefan Bürk 4 months ago
· Edited
TYPO3 will not implement CROSS JOIN on it's own. We would end in nearly completly
writing the QueryBuilder on our own then. That said, you should headover to the
Doctrine DBAL repository and suggest that to Doctrine first.
To be honest, I doubt it will be implemented there either.
On our side, the maintenance burdon would drasticilly increase
due to having "cloning" final classes, maintaining own state,
implementig all the query building and keeping it up2date.
Doctrine strives to implement only stuff which is cross-dbmns compatible for
all supported database systems.
With the current solution your only way is to use the `*join()` methods or
use from() multiple times.
Basically, you can use an innerJoin() just fine without a join condition,
which acts exactly the same as a cross join (for mariadb, mysql and sqlite)
. CROSS JOIN is just a simplyfied solution there. However, PostgreSQL needs
a special treatment thus you need a conditional condition based on the
connection database platform and use a `ON true` condition.
I'm going to reject that feature for now and we will see if doctrine
implements that if you asked them gently. If you want to discuss further
head over to reopen the issue.