Doctrine Query Builder with joins resulting in: "Unknown column id in 'on clause'&quo-CodePudding

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.

CodePudding user response：

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();

CodePudding user response：

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.