php - CakePHP的contain()方法看到同一个表的belongsToMany而不是belongsTo

Question

所以我有以下(简化的)模型

                                +------------------+
                                | project_statuses |
                                +------------------+
+---------------------+    +----| id               |
| projects            |    |    | name             |
+---------------------+    |    +------------------+ 
| id                  |    |    +---------+                
| name                |    |    | clients |            
| project_statuses_id |----+    +---------+                               
| client_id           |---------| id      |            
+---------------------+         | name    |
           |                    +---------+
         +------------------+      |
         | clients_projects |------+
         +------------------+
         | id               |
         | client_id        |
         | project_id       |
         +------------------+

其中一个项目属于多个客户端，一个客户端可以拥有多个项目，但只有一个客户端 (Projects.client_id) 可以负责一个项目。这里的项目状态仅供比较。

因此，我的 ProjectsTable.php、ClientsTabe.php 和 ProjectStatusesTable.php 中的关联如下所示

// In ProjectsTable.php
$this->belongsTo('ProjectStatuses', [
    'foreignKey' => 'project_status_id',
    'joinType' => 'INNER'
]);
$this->belongsTo('Clients', [
    'foreignKey' => 'client_id',
    'joinType' => 'INNER'
]);
$this->belongsToMany('Clients', [
    'foreignKey' => 'project_id',
    'targetForeignKey' => 'client_id',
    'joinTable' => 'clients_projects'
]);

// In ClientsTabe.php
$this->hasMany('Projects', [
    'foreignKey' => 'client_id'
]);
$this->belongsToMany('Projects', [
    'foreignKey' => 'client_id',
    'targetForeignKey' => 'project_id',
    'joinTable' => 'clients_projects'
]);

// In ProjectStatusesTable.php
$this->hasMany('Projects', [
    'foreignKey' => 'project_status_id'
]);

现在，在我的 projects/index 页面中，我希望有一个表格，显示项目 ID、名称、状态和负责人。所以我想到了这样的事情

// In ProjectsController.php
$this->Projects->find()->select(['Projects.id','Projects.name'])
->contain(['ProjectStatuses' => [
    'fields' => [
      'ProjectStatuses.name',
    ]]])
->contain(['Clients' => [
    'fields' => [
      'Clients.name',
    ]]]);

但仅获取ProjectStatuses.name，对于Clients.name，它会抛出错误您需要选择“ClientsProjects.project_id”字段) 告诉我这是通过 belongsToMany 关联而不是 belongsTo 关联进行查找。

事实上，如果我只编写 ->contain('Clients') 而不是指定 Clients.name 字段，它会发送以下查询

SELECT 
 Projects.id AS `Projects__id`, 
 Projects.name AS `Projects__name`, 
 ProjectStatuses.name AS `ProjectStatuses__name` 
FROM 
 projects Projects 
INNER JOIN project_statuses ProjectStatuses ON ProjectStatuses.id = (Projects.project_status_id) 

SELECT 
 ClientsProjects.id AS `Clients_CJoin__id`, 
 ClientsProjects.client_id AS `Clients_CJoin__client_id`, 
 ClientsProjects.project_id AS `Clients_CJoin__project_id`, 
 Clients.id AS `Clients__id`, 
 Clients.name AS `Clients__name`,  
FROM 
 clients Clients 
INNER JOIN clients_projects ClientsProjects ON Clients.id = (ClientsProjects.client_id) 
WHERE 
 ClientsProjects.project_id in (4)

如何告诉查询对象通过 Projects.client_id 获取 Clients.name，就像我通过 ProjectStatuses.name 获取 Projects.project_statuses_id 而不是通过 ClientProjects 表传递？

Answer 1

在您的项目表中，您为客户创建了两个关联，反之亦然。这是行不通的(正如《高地人》所说，“只能有一个”)，而且很可能是你所有问题的根源。

您需要更改每个表中的其中一个关联。也许一个项目应该belongsToMany客户，但只belongTo一个ReponsibleClient，而一个客户应该belongsToMany项目和hasManyReponsibleProjects？