模型本身，双向 [英] Model related to itself, bi-directional

问题描述

我有一个people表，其中人们通过id与其他人相关联。我相信这应该用一个单独的表作为这种关系的标准，但我想要能够检索相关的人，当查看单个记录，无论该记录存储为哪个键。例如：

I have a people table, where people are associated with other people by id. I believe this should be done with a separate table as is standard for such relationships, but I want to be able to retrieve the associated people when viewing a single record regardless of which key this record was stored as. For instance:

table: people
id | first_name | last_name

table: people_associations
id | person_one_id | person_two_id

model: person
$hasMany = array(
    "Associates" => array(
        "className" => "Person",
        "foreignKey" => ????
    )
);

如何使 Associates 引用foreignKey是 person_one_id 还是 person_two_id 的关系，并将所有结果返回一个整齐的数组？

How do I make this so that Associates references the relationship whether the foreignKey is person_one_id or person_two_id, and return all the results in one neat array?

推荐答案

我相信我设法实现了一个解决方案。关键部分是使用一个合适的finderQuery，它检查user_association表中的用户相关字段，并重新排列first_id和second_id字段，以确保Cake只能使用一个外键字段链接所有关联

I believe I managed to implement a solution. The key part is to use a proper finderQuery which checks both user related fields in the user_association table and it also rearranges the "first_id" and "second_id" fields order to make sure Cake is able to link all associations using just one foreign key field ("first_id" in this example).

请注意我使用了以下表格：

Please note I used the following tables:

table: users
id | name

table: user_associations
id | first_id | second_id

1。定义UserAssociation模型

class UserAssociation extends AppModel {

    public $belongsTo = array(
        'Second' => array(
            'className' => 'User',
            'foreignKey' => 'second_id',
        )
    );
}

2。定义用户模型

class User extends AppModel {

    //fields definition skipped for brevity

    public $hasMany = array(
        'UserAssociations' => array(
            'className' => 'UserAssociation',
            'foreignKey' => 'first_id',
            'finderQuery' => 'SELECT * FROM (SELECT UserAssociations.id, UserAssociations.first_id, UserAssociations.second_id 
                FROM user_associations as UserAssociations 
                WHERE (UserAssociations.first_id = {$__cakeID__$})
                UNION 
                SELECT UserAssociations2.id, UserAssociations2.second_id as first_id, UserAssociations2.first_id as second_id 
                FROM user_associations as UserAssociations2 
                WHERE (UserAssociations2.second_id = {$__cakeID__$}) ) as UserAssociations '
        )
    );
}

3。结果

现在，当我运行如下的示例查询时：

Now, when I run a sample query as follows:

$this->User->recursive = 2;
$this->set('user_data', $this->User->read());

我将UserAssociations作为结果的一部分与相关人员记录可用 - 这是var_dump user_data）output in a view：

I get UserAssociations as a part of the result with related people records available - this is the var_dump($user_data) output as captured in a view:

array (size=3)
  'User' => 
    array (size=2)
      'id' => string '1' (length=1)
      'name' => string 'John Doe' (length=8)
  'UserAssociations' => 
    array (size=2)
      0 => 
        array (size=4)
          'id' => string '1' (length=1)
          'first_id' => string '1' (length=1)
          'second_id' => string '18' (length=2)
          'Second' => 
            array (size=2)
              ...
      1 => 
        array (size=4)
          'id' => string '2' (length=1)
          'first_id' => string '1' (length=1)
          'second_id' => string '17' (length=2)
          'Second' => 
            array (size=2)
              ...

希望这有帮助！

这篇关于模型本身，双向的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！