Doctrine Query Builder with joins resulting in: "Unknown column id in 'on clause'"

Question

Summary

I'm trying to use Doctrine's Query Builder to make a SELECT that includes a number of joins.

This works fine when all the joins are done through entity relationships, but when I introduce a join not through a relationship (campaign_instance below), it results in a bad MySQL query.

The error is "Unknown column 'w3_.id' in 'on clause'". w3_ refers to a table that absolutely has an id column.

The code

This query builder:

$this->entity_manager->getRepository(CampaignStep::class)
    ->createQueryBuilder('campgain_steps_available_for_completion')
    ->select('step')
    ->from(CampaignStep::class, 'step')
    ->join('step.group', 'campaign_group')
    ->join('campaign_group.campaign', 'campaign')
    ->join(
        CampaignInstance::class,
        'campaign_instance',
        'WITH',
        'campaign_instance.campaign = campaign')
    ->getQuery()
    ->execute();

results in this MySQL query:

SELECT
  t0_.id AS id_0,
  t0_.slug AS slug_1,
  t0_.description AS description_2,
  t0_.active AS active_3,
  t0_.created_at AS created_at_4,
  t0_.step_completion_min AS step_completion_min_5,
  t0_.ordinal AS ordinal_6,
  t0_.dtype AS dtype_7,
  t0_.mobile_image_id AS mobile_image_id_8,
  t0_.web_image_id AS web_image_id_9,
  t0_.group_id AS group_id_10
FROM
  taxonomy_topic t1_
  INNER JOIN campaign_instance w2_ ON (w2_.campaign_id = w3_.id),
  taxonomy_topic t0_
  INNER JOIN campaign_group w4_ ON t0_.group_id = w4_.id
  INNER JOIN campaign w3_ ON w4_.campaign_id = w3_.id
  AND w3_.dtype IN ('campaign')
WHERE
  (
    t1_.dtype IN ('campaignstep')
    AND t0_.dtype IN ('campaignstep')
  )

...Which results in this error:

Unknown column 'w3_.id' in 'on clause'

Entity summary

Here are the entity relationships:

Campaign <-1-M-> CampaignGroup <-1-M-> CampaignStep

Campaign <-1-M- CampaignInstance

I'm selecting CampaignSteps, joining them to CampaignGroup then Campaign through their entity relationships. That much works.

Then I want to join from Campaign to CampaignInstance. Since that relationship is unidirectional (owning side CampaignInstance), I can't use a Campaign relationship to get there. Adding this:

    ->join(
        CampaignInstance::class,
        'campaign_instance',
        'WITH',
        'campaign_instance.campaign = campaign')

to the Query Builder breaks the resulting query.

How do I fix this?

Packages

composer show doctrine/*:

doctrine/annotations                1.13.2 Docblock Annotations Parser
doctrine/cache                      1.12.1 PHP Doctrine Cache library is a popular cache implementation that supports ...
doctrine/collections                1.6.8  PHP Doctrine Collections library that adds additional functionality on top ...
doctrine/common                     2.13.3 PHP Doctrine Common project is a library that provides additional functiona...
doctrine/dbal                       2.13.7 Powerful PHP database abstraction layer (DBAL) with many features for datab...
doctrine/deprecations               v0.5.3 A small layer on top of trigger_error(E_USER_DEPRECATED) or PSR-3 logging w...
doctrine/doctrine-bundle            2.3.2  Symfony DoctrineBundle
doctrine/doctrine-migrations-bundle 2.2.3  Symfony DoctrineMigrationsBundle
doctrine/event-manager              1.1.1  The Doctrine Event Manager is a simple PHP event system that was built to b...
doctrine/inflector                  1.4.4  PHP Doctrine Inflector is a small library that can perform string manipulat...
doctrine/instantiator               1.4.0  A small, lightweight utility to instantiate objects in PHP without invoking...
doctrine/lexer                      1.2.3  PHP Doctrine Lexer parser library that can be used in Top-Down, Recursive D...
doctrine/migrations                 2.3.5  PHP Doctrine Migrations project offer additional functionality on top of th...
doctrine/orm                        2.7.5  Object-Relational-Mapper for PHP
doctrine/persistence                1.3.8  The Doctrine Persistence project is a set of shared interfaces and function...
doctrine/reflection                 1.2.2  The Doctrine Reflection project is a simple library used by the various Doc...
doctrine/sql-formatter              1.1.2  a PHP SQL highlighting library

---

What I've tried so far:

This seems to have something to do with the multiple FROM clauses.

If I remove the first clause taxonomy_topic t1_ INNER JOIN workspace_instance w2_ from the FROM (and its related WHERE clause), I get a valid query that gives me exactly what I'm looking for:

SELECT
  t0_.id AS id_0,
  t0_.slug AS slug_1,
  t0_.description AS description_2,
  t0_.active AS active_3,
  t0_.created_at AS created_at_4,
  t0_.step_completion_min AS step_completion_min_5,
  t0_.ordinal AS ordinal_6,
  t0_.dtype AS dtype_7,
  t0_.mobile_image_id AS mobile_image_id_8,
  t0_.web_image_id AS web_image_id_9,
  t0_.group_id AS group_id_10
FROM
  taxonomy_topic t0_
  INNER JOIN workspace_group w4_ ON t0_.group_id = w4_.id
  INNER JOIN workspace w3_ ON w4_.campaign_id = w3_.id
  AND w3_.dtype IN ('campaign')
WHERE
  (
    t0_.dtype IN ('campaignstep')
  )

However, I don't know how to alter the Doctrine Query Builder to result in that MySQL.

Answer 1

Figured it out.

Since I'm creating the Query Builder from the CampaignStep repository, the CampaignStep select was implicit. By adding ->select('step')->from(CampaignStep::class, 'step'), it was trying to join CampaignStep again, resulting in the multiple FROM clauses that broke the query.

This is the fixed Query Builder:

$this->mymeq_em->getRepository(CampaignStep::class)
    ->createQueryBuilder('step')
    ->join('step.group', 'campaign_group')
    ->join('campaign_group.campaign', 'workspace')
    ->join(
        WorkspaceInstance::class,
        'workspace_instance',
        'WITH',
        'workspace_instance.workspace = workspace')
    ->getQuery()
    ->execute();

Answer 2

Read https://dev.mysql.com/doc/refman/8.0/en/join.html in the sections where it says:

An ON clause can refer only to its operands.

JOIN has higher precedence than the comma operator (,)

I don't use Doctrine, so I can't advise on how to make its query builder do what you want. But the semantics of JOIN documented at that manual page explain why you got the errors you saw.