CakePHP 1.3 - where 子句中的未知列

2021-12-21 00:00:00 sql php mysql cakephp cakephp-1.3

我正在处理一个已经存在的 cakephp 1.3 项目，我需要向数据库添加一个新表.我的控制器中有这个:

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'));

它给了我这个错误:

  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

显然我的控制器代码是错误的，但我不确定如何将电子邮件表与 ShootingPlacement 表相关联.我认为我的模型是正确的.到目前为止，如果我有这个:

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'));

它将从 Shooting、ShootingPlacement 和 Person 中检索行，我也希望 Email 出现在那里.电子邮件有 2 个外键:一个来自 ShootinPlacement，一个来自 Person.

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'
        )
    );
}

我需要在视图中循环遍历 ShootingPlacement 变量，我需要它包含 ShootingPlacement 和 Person 的特定 id 的电子邮件表数据(正如您在查询中看到的，Person 和 ShootingPlacement 已经处于关系中，我只需要有电子邮件)

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)

推荐答案

你应该非常小心你所追求的关系.快速浏览其中的一些答案，他们似乎建议您简单地将 Email 模型的连接添加到您的 Person 模型中，并依靠您的查找条件来确保您的查询不会占用您服务器的内存.

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.

我将假设首先，您希望此电子邮件关系隐含在您对 Person 的所有查询中，否则您只需指定您想要的每个查询的连接.在这种情况下，您肯定希望使用 模型关系.

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.

您的代码显示 Shooting 和 ShootingPlacement(假设这是一个模型到模型的映射关系)都属于两个模型.顺便说一下，射击 belongsTo 发射 - 我们在这里还没有看到.我认为这不适用于当前场景.

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.

现在，让我们假设因为您的电子邮件表有外键，它将是一个 hasOne 关系，而不是一个 hasMany 关系> - 所以这就是你需要链接的东西.我将把它链接到 ShootingPlacement 模型，因为这是您要查询的模型，所以它应该是模型围绕它连接的中心点.结构明智，因为一切似乎都源自您的 Person 模型，我不得不建议您改为查询 那个 模型.但到目前为止，它的设置方式将允许您从几乎任何地方进行查询，并且仍然检索几乎相同的结果，除了一些模型名称和表别名.

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.

纯粹是因为你的 Email 和 ShootingPlacement 之间的外键名称不同，而 CakePHP 1.3 不能很好地处理这个问题，我也建议你不要使用外键，而是将它放入关系中作为条件.

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).

现在，当您创建电子邮件模型时，我不建议通过再次将其链接回 ShootingPlacement 来进一步复杂化这些纠缠模型.正如您所说，它还有一个指向 Person 模型的外键.因此，您可能希望对 Person 模型执行与上述完全相同的操作(当然，更改条件以反映 Person 外键).这样你的模型会更灵活一些；它仍然会加入到 ShootingPlacement 和 Person，并且还允许您在没有其他关联模型的情况下单独查询它.

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.

文档

  • CakePHP 1.3 模型关联
  • CakePHP 1.3 可包含行为

    • 另见

    • 这篇关于 Stack 的文章

相关文章