Issue Feature Rejected

#99415Missing QueryBuilder feature: CROSS JOIN (JOIN without ON)

Leonie Philine 12/22/22, 19:40 updated 08/06/24, 08:44

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.

Reviews 0

No change resolves or names this issue in its commit message, and no comment links one.

History 3

  1. Leonie Philine commented 12/22/22, 21:44

    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)

  2. Stefan Bürk commented 08/06/24, 08:44

    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.

  3. Stefan Bürk changed status New Rejected 08/06/24, 08:44