我正在处理一个已经存在的cakehp1.3项目,我需要向数据库中添加一个新表。我的控制器里有这个:
$conditions = array('ShootingPlacement.person_id' => $id, 'Email.person_id' => $id, 'Email.shooting_placement_id' => 'ShootingPlacement.id');
$shootingPlacements = $this->ShootingPlacement->find('all', compact('conditions'));
它给了我一个错误:
Warning (512): SQL Error: 1054: Unknown column 'Email.person_id' in 'where clause' [CORE/cake/libs/model/datasources/dbo_source.php, line 684]
这就是它试图创建的查询:
SELECT `ShootingPlacement`.`id`, ... FROM `shooting_placements` AS `ShootingPlacement`
LEFT JOIN `people` AS `Person` ON (`ShootingPlacement`.`person_id` = `Person`.`id`)
LEFT JOIN `shootings` AS `Shooting` ON (`ShootingPlacement`.`shooting_id` = `Shooting`.`id`)
WHERE `ShootingPlacement`.`person_id` = 123688 AND `Email`.`person_id` = 123688 AND `Email`.`shooting_placement_id` = 'ShootingPlacement.id'
ORDER BY `lastname` ASC
显然,我的控制器代码是错误的,但我不确定如何将电子邮件表与ShootingPlacement表关联起来。我认为我的模型是正确的。到目前为止,如果我有这个:
$conditions = array('ShootingPlacement.person_id' => $id);
$shootingPlacements = $this->ShootingPlacement->find('all', compact('conditions'));
它将从Shooting、ShootingPlacement和Person中检索行,我希望电子邮件也在那里。电子邮件有两个外键:一个来自ShootinPlacement,一个来自Person。
这些是模型,我创建的唯一一个是电子邮件,其余的都能正常工作。
class Email extends AppModel
{
var $name = 'Email';
var $belongsTo = array
(
'Person' => array
(
'className' => 'Person',
'foreignKey' => 'person_id'
),
'ShootingPlacement' => array
(
'className' => 'ShootingPlacement',
'foreignKey' => 'shooting_placement_id'
)
);
}
class ShootingPlacement extends AppModel
{
var $name = 'ShootingPlacement';
var $belongsTo = array
(
'Person' => array
(
'className' => 'Person',
'foreignKey' => 'person_id',
'order' => 'lastname ASC'
),
'Shooting' => array
(
'className' => 'Shooting',
'foreignKey' => 'shooting_id'
)
);
}
class Person extends AppModel
{
var $name = 'Person';
var $belongsTo = array
(
'PersonOrigin' => array
(
'className' => 'PersonOrigin',
'foreignKey' => 'person_origin_id'
)
);
var $hasMany = array
(
'ShootingPlacement' => array
(
'className' => 'ShootingPlacement',
'foreignKey' => 'person_id',
'dependent' => false
)
);
}
class Shooting extends AppModel
{
var $name = 'Shooting';
var $belongsTo = array
(
'ShootingLocation' => array
(
'className' => 'ShootingLocation',
'foreignKey' => 'shooting_location_id'
),
'Emission' => array
(
'className' => 'Emission',
'foreignKey' => 'emission_id'
)
);
}
我在视图中需要的是循环浏览ShootingPlacement变量,并且我需要它包含ShootingPlacement和Person的特定id的电子邮件表数据(正如您在查询中看到的,Person和ShootingPosition已经处于关系中,我只需要有电子邮件)
你应该非常小心你所追求的关系。快速浏览一下其中的一些答案,它们似乎建议您只需将电子邮件模型的联接添加到个人模型中,并根据查找的条件来确保您的查询不会占用服务器的内存。
我假设,首先,您希望此电子邮件关系隐含在您对Person的所有查询中,否则您可以简单地为每个查询指定联接。在这种情况下,您肯定希望使用模型关系将其链接起来。
您的代码显示Shooting和ShootingPlacement(假定这是一种模型到模型的映射关系)都属于两个模型。顺便说一句,拍摄belongsTo
发射——我们还没有在这里看到。我认为这不适用于当前的情况。
现在,让我们假设坏的情况是,因为您的电子邮件表有外键,它将是一个hasOne
关系,而不是hasMany
,所以这就是您需要链接它的依据。我将它链接到ShootingPlacement模型,因为这是您要查询的模型,所以它应该是围绕它连接模型的中心点,因为一切似乎都源于您的Person模型,所以我不得不建议您查询该模型。但到目前为止,它的设置方式将允许您从几乎任何地方进行查询,并且除了一些模型名称和表别名之外,仍然可以检索到基本相同的结果。
纯粹是因为电子邮件和ShootingPlacement之间的外键有一个不同的名称,而CakePHP 1.3不能很好地处理这个问题,我还建议你不要使用外键,而是将其作为条件放入关系中。
class ShootingPlacement extends AppModel
{
var $name = 'ShootingPlacement';
var $actsAs = array('Containable');
var $hasOne = array(
'Email' => array(
'className' => 'Email',
'foreignKey' => false,
'conditions' => array(
'Email.shooting_placement_id = ShootingPlacement.id',
'Email.person_id = ShootingPlacement.person_id'
)
)
);
var $belongsTo = array (
'Person' => array (
'className' => 'Person',
'foreignKey' => 'person_id',
'order' => 'lastname ASC'
),
'Shooting' => array (
'className' => 'Shooting',
'foreignKey' => 'shooting_id'
)
);
}
我还添加了可控制的行为。这允许您从每个查询中控制要返回哪些关联模型以及主模型结果。它将默认为所有,但当您只需要特定的内容和/或出于内存原因时,它会很方便(如果您不限制这些查询或只指定要返回的字段名,这些类型的查询会很快破坏服务器内存)。
现在,当您创建电子邮件模型时,我不建议通过再次将其链接回ShootingPlacement来进一步复杂化这一混乱的纠缠模型。正如您所说,它还有一个Person模型的外键。因此,您可能希望对您的Person模型执行与上面完全相同的操作(当然,更改条件以反映Person外键)。通过这种方式,您的模型更加灵活;它仍然会加入ShootingPlacement和Person,并且如果需要,还允许您在没有其他关联模型的情况下单独查询它。
文档
- CakePHP 1.3模型关联
- CakePHP 1.3可控制行为
另请参见
- Stack上的这篇文章
在您的模型中添加可包含的行为
class Email extends AppModel {
var $name = 'Email';
var $actsAs = array('Containable');
var $belongsTo = array
(
'Person' => array
(
'className' => 'Person',
'foreignKey' => 'person_id'
),
'ShootingPlacement' => array
(
'className' => 'ShootingPlacement',
'foreignKey' => 'shooting_placement_id'
)
);
}
只需在控制器中编写以下代码。
$this->ShootingPlacement->recursive = 2;
$this->ShootingPlacement->contain = array(
'Shooting',
'Person' => array(
'Email'
)
);
$conditions = array(
'ShootingPlacement.person_id' => $id,
'Email.shooting_placement_id' => 'ShootingPlacement.id'
);
$shootingPlacements = $this->ShootingPlacement->find('all', compact('conditions'));
希望这对你有帮助。
您需要将您的模型ShootingPlacement与您所称的"电子邮件"链接起来。
class ShootingPlacement extends AppModel
var $name = 'Shooting';
var $hasMany= array
(
'Email' => array
(
'className' => 'Email',
'foreignKey' => 'yourfk'
),
);
}
并使用它非常强大的可传染行为!
示例:
$contain=array('Email'=>array('fields'=>array('id','...')));
$conditions=array('ShootingPlacement.id'=>$yourId);
$this->ShootingPlacement->attachBehaviros('Containable');
$this->ShootingPlacement->find('all',$conditions);// your will retrieve yoru SHootingItem + Emails linked
添加$hasOne关系到具有类似以下的电子邮件的Person模型
var $hasOne = array(
'Email' => array(
'className' => 'Email',
'foreignKey' => 'person_id' // Column defined for person ids in Email table
)
);
然后添加
$this->ShootingPlacement->recursive = 2;
或您可以简单地使用cakepp中的联接来联接电子邮件模型。参考cakehpp连接表
这将提供所需的联接:
$conditions = array('ShootingPlacement.person_id' => $id, 'Email.person_id' => $id, 'Email.shooting_placement_id' => 'ShootingPlacement.id');
$joins = array(
array(
'table' => 'emails',
'alias' => 'Email',
'type' => 'LEFT',
'conditions' => array('Email.shooting_placement_id = ShootingPlacement.id')
)
);
$shootingPlacements = $this->ShootingPlacement->find('all',
array(
'conditions' => $conditions,
'joins' => $joins
)
);