CakePHP 1.3 - where子句中的未知列 [英] CakePHP 1.3 - Unknown column in where clause
问题描述
我正在一个已经存在的cakephp 1.3项目，我需要添加一个新的表到数据库。我在我的控制器中有这个：
$ conditions = array（'ShootingPlacement.person_id'=> $ id，'Email。 person_id'=> $ id，'Email.shooting_placement_id'=>'ShootingPlacement.id'）;
$ shootingPlacements = $ this-> ShootingPlacement-> find（'all'，compact（'conditions'））;
它给我这个错误：
警告（512）：SQL错误：1054：'where子句'中的未知列'Email.person_id'[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`.`personperson` = 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中检索行，我想电子邮件也在那里。电子邮件有2个外键：一个来自ShootinPlacement，一个来自Person。
这些是模型，我创建的唯一一个是电子邮件，其余的正确工作。
class Email extends AppModel
{
var $ name ='Email';
var $ belongsTo = array
（
'Person'=> array
（
'className'=>'Person '，
'foreignKey'=>'person_id'
），
'ShootingPlacement'=> array
（
'className'=& ，
'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'=& b（
'className'=>'Shooting'，
'foreignKey'=>'shooting_id'
）
）;
}
类Person扩展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
）
）;
}
类射击扩展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和ShootingPlacement已经在一个关系中，我只需要有电子邮件）
你应该非常小心你的关系。从快速浏览一些这些答案，他们似乎建议你只需添加一个联系到电子邮件模型到您的人物模型，并依靠您的发现的条件，以确保您的查询不会扫清您的服务器的内存。
我想假设首先，您希望此电子邮件关系在您对Person的所有查询中都是隐含的，否则您只需指定在你想要的每个查询的联接。在这种情况下，您一定要使用模型关系。
您的代码显示Shooting和ShootingPlacement（假定这是一个模型映射关系的模型）到两个模型。顺便说一句，拍摄 belongsTo 排放 - 我们在这里还没有看到。我假设这不适用于当前的情况。
现在，让我们假设坏的，因为你的电子邮件表有外键它将是一个 hasOne 关系，而不是一个 hasMany - 所以这是你需要链接。我要将它链接到ShootingPlacement模型，因为这是您正在查询的模型，因此它应该是围绕它连接模型的中心点。结构明智，因为一切似乎都源自你的Person模型，我将建议你查询 模型。但是到目前为止，它设置的方式将允许你从几乎任何地方查询，仍然检索大部分相同的结果栏几个模型名称和表别名。
纯粹是因为你的电子邮件和ShootingPlacement之间的外键有不同的名称，而CakePHP 1.3没有处理这个很好，我也建议你不要使用外键，而是把它放在关系中 conditions 。
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，并且还将允许您在没有其他关联模型的情况下根据需要单独查询。
文档
b ul>
I'm working on an already existing cakephp 1.3 project and I needed to add a new table to the database. I have this in my controller:
$conditions = array('ShootingPlacement.person_id' => $id, 'Email.person_id' => $id, 'Email.shooting_placement_id' => 'ShootingPlacement.id');
$shootingPlacements = $this->ShootingPlacement->find('all', compact('conditions'));
And it's giving me this error:
Warning (512): SQL Error: 1054: Unknown column 'Email.person_id' in 'where clause' [CORE/cake/libs/model/datasources/dbo_source.php, line 684]
And ths is the query it's trying to create:
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
Obviously my controller code is wrong, but I'm not sure how to relate the Email table to the ShootingPlacement one. I think my models are correct. So far if I have this:
$conditions = array('ShootingPlacement.person_id' => $id);
$shootingPlacements = $this->ShootingPlacement->find('all', compact('conditions'));
It will retrieve the rows from Shooting, ShootingPlacement and Person, I want Email to be there too. Email has 2 foreign keys: one from ShootinPlacement and one from Person.
These are the models, the only one I created is Email, the rest where working correctly.
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'
)
);
}
What I need on the view is to loop through the ShootingPlacement variable and I need it to contain the Email table data for that specific id of ShootingPlacement and Person (As you see in the query, Person and ShootingPlacement are in a relationship already, I only need there to be Email too)
You should be very careful with the relationship you're after. From a quick glance at some of these answers, they seem to suggest you simply add a join to the Email model into your Person model and rely on the conditions of your find to ensure your query doesn't ransack your server's memory.
I'm going to assume that first of all, you want this Email relationship to be implicit in all your queries on Person, otherwise you could simply specify the join on each query you wanted it for. In this case, you definitely want to link it using model relationships.
Your code shows that Shooting and ShootingPlacement (presume this is a model to model mapping relationship) both belong to two models. Incidentally, Shooting belongsTo Emission - which we haven't seen here yet. I assume this isn't applicable to the current scenario.
Now, let's assume off the bad that because your Email table has foreign keys, it will be a hasOne relationship, rather than a hasMany - so that's what you need to link it by. I'm going to link it to the ShootingPlacement model because this is the model you are querying, so it should be the central point at which models are joined around it. Structure wise, because everything seems to originate from your Person model, I would have to suggest you query that model instead. But the way it's set up so far will allow you to query from nearly anywhere and still retrieve mostly the same results bar a few model names and table aliases.
Purely because your foreign key between Email and ShootingPlacement has a different name, and CakePHP 1.3 doesn't handle this very well, I'm also going to suggest you don't use a foreign key, instead putting it into the relationship as conditions.
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'
)
);
}
I've also added the containable behaviour in there. This allows you to control from each query which associated models you'd like to return with your primary model results. It will default to all, but can be handy when you only want something specific and/or for memory reasons (these kinds of queries can destroy your server memory pretty quickly if you don't limit them or specify only the field names you want to return).
Now when you create your Email model, I wouldn't suggest complicating this mess of entangled models any further by linking it back to ShootingPlacement again. As you've said, it also has a foreign key to the Person model. So you might want to do exactly the same thing as above for your Person model (changing the conditions to reflect the Person foreign key of course). This way your model is a little more flexible; it will still join to ShootingPlacement and Person, and will also allow you to query it seperately if required without the other associated models.
Documentation
See also
这篇关于CakePHP 1.3 - where子句中的未知列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
