Feature #99415
open
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 over 1 year 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)