cakephp contain()方法在同一个表的belongsto上看到belongstomany

oiopk7p5  于 2021-06-18  发布在  Mysql
关注(0)|答案(1)|浏览(426)

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

+------------------+
                                | 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 它抛出错误 You are required to select the "ClientsProjects.project_id" field(s) 告诉我那是通过 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)

如何告诉查询对象获取 Clients.name 通过 Projects.client_id 就像我得到的一样 ProjectStatuses.name 通过 Projects.project_statuses_id 而不是路过 ClientProjects table?

yebdmbv4

yebdmbv41#

在projects表中,为客户机创建了两个关联,反之亦然。这是行不通的(正如高地人所说,“可能只有一个”),而且很可能是你所有问题的根源。
您需要更改每个表中的一个关联。也许一个项目应该 belongsToMany 客户,但仅限于 belongTo 一个负责的客户,一个客户应该 belongsToMany 项目和 hasMany 负责的项目?

相关问题