带有联接的理论查询生成器导致：“'on子句‘中的未知列id”

Question

摘要

我正在尝试使用Doctrine的Query来进行包含多个联接的选择。

当所有的连接都是通过实体关系完成时，这很好，但是当我引入一个连接而不是通过一个关系(下面的campaign_instance)时，它会导致一个糟糕的MySQL查询。

错误是"Unknown column 'w3_.id' in 'on clause'“。w3_指的是绝对有id列的表。

密码

这个查询生成器：

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

这个MySQL查询的结果是：

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导致此错误：

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

实体摘要

以下是实体关系：

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

Campaign <-1-M- CampaignInstance

我选择CampaignStep，将它们加入到CampaignGroup，然后通过它们的实体关系将Campaign加入其中。那么多就行了。

然后我想加入从Campaign到CampaignInstance。因为这种关系是单向的(拥有侧CampaignInstance)，所以我不能使用Campaign关系来达到这个目的。补充如下：

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

到query，则会中断结果查询。

我该怎么解决这个问题？

套餐

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

到目前为止我尝试过的是：

这似乎与复数FROM子句有关。

如果我从from (及其相关WHERE子句)中删除第一个子句taxonomy_topic t1_ INNER JOIN workspace_instance w2_，我将得到一个有效的查询，该查询提供了我所要寻找的内容：

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')
  )

但是，我不知道如何修改来生成这个MySQL。

Answer 1

弄明白了。

由于我是从CampaignStep存储库创建查询生成器，所以CampaignStep选择是隐式的。通过添加->select('step')->from(CampaignStep::class, 'step')，它再次尝试加入CampaignStep，从而导致多个FROM子句破坏了查询。

这是固定的查询生成器：

$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

请阅读“https://dev.mysql.com/doc/refman/8.0/en/join.html”一节中的内容：

ON子句只能引用其操作数。JOIN的优先级高于逗号运算符(,)。

我不使用Doctrine，所以我不能建议如何让它的查询生成器做您想做的事情。但是，手册页中记录的联接的语义解释了为什么您看到了错误。