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

Question

总结

我正在尝试使用 Doctrine 的查询生成器来创建包含多个联接的 SELECT。

当所有联接都是通过实体关系完成时，这种方法工作得很好，但是当我不通过关系引入联接（下面的campaign_instance）时，它会导致错误的 MySQL 查询。

错误是“‘on 子句’中存在未知列‘w3_.id’”。 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')
  )

...这会导致此错误：

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

实体摘要

以下是实体关系：

Campaign <-1-M->< /code> 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')

到查询生成器会破坏生成的查询。

我该如何解决这个问题？

Packages

composer showdoctrine/*：

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

但是，我不不知道如何更改 Doctrine Query Builder 以生成该 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，因此我无法建议如何使其查询生成器执行您想要的操作。但是该手册页中记录的 JOIN 语义解释了为什么您会收到所看到的错误。