問題描述

我正在處理一個(gè)已經(jīng)存在的 cakephp 1.3 項(xiàng)目，我需要向數(shù)據(jù)庫添加一個(gè)新表.我的控制器中有這個(gè):

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

它給了我這個(gè)錯(cuò)誤:

  Warning (512): SQL Error: 1054: Unknown column 'Email.person_id' in 'where clause' [CORE/cake/libs/model/datasources/dbo_source.php, line 684]

這是它試圖創(chuàng)建的查詢:

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  

顯然我的控制器代碼是錯(cuò)誤的，但我不確定如何將電子郵件表與 ShootingPlacement 表相關(guān)聯(lián).我認(rèn)為我的模型是正確的.到目前為止，如果我有這個(gè):

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 出現(xiàn)在那里.電子郵件有 2 個(gè)外鍵:一個(gè)來自 ShootinPlacement，一個(gè)來自 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.

這些是模型，我創(chuàng)建的唯一一個(gè)是電子郵件，其余的可以正常工作.

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

我需要在視圖中循環(huán)遍歷 ShootingPlacement 變量，我需要它包含 ShootingPlacement 和 Person 的特定 id 的電子郵件表數(shù)據(jù)(正如您在查詢中看到的，Person 和 ShootingPlacement 已經(jīng)處于關(guān)系中，我只需要有電子郵件)

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)

推薦答案

你應(yīng)該非常小心你所追求的關(guān)系.快速瀏覽其中的一些答案，他們似乎建議您簡單地將 Email 模型的連接添加到您的 Person 模型中，并依靠您的查找條件來確保您的查詢不會(huì)占用您服務(wù)器的內(nèi)存.

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.

我將假設(shè)首先，您希望此電子郵件關(guān)系隱含在您對(duì) Person 的所有查詢中，否則您只需指定您想要的每個(gè)查詢的連接.在這種情況下，您肯定希望使用 模型關(guān)系.

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(假設(shè)這是一個(gè)模型到模型的映射關(guān)系)都屬于兩個(gè)模型.順便說一下，射擊 belongsTo 發(fā)射 - 我們?cè)谶@里還沒有看到.我認(rèn)為這不適用于當(dāng)前場(chǎng)景.

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.

現(xiàn)在，讓我們假設(shè)因?yàn)槟碾娮余]件表有外鍵，它將是一個(gè) hasOne 關(guān)系，而不是一個(gè) hasMany 關(guān)系> - 所以這就是你需要鏈接的東西.我將把它鏈接到 ShootingPlacement 模型，因?yàn)檫@是您要查詢的模型，所以它應(yīng)該是模型圍繞它連接的中心點(diǎn).結(jié)構(gòu)明智，因?yàn)橐磺兴坪醵荚醋阅?Person 模型，我不得不建議您改為查詢 那個(gè) 模型.但到目前為止，它的設(shè)置方式將允許您從幾乎任何地方進(jìn)行查詢，并且仍然檢索幾乎相同的結(jié)果，除了一些模型名稱和表別名.

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.

純粹是因?yàn)槟愕?Email 和 ShootingPlacement 之間的外鍵名稱不同，而 CakePHP 1.3 不能很好地處理這個(gè)問題，我也建議你不要使用外鍵，而是將它放入關(guān)系中作為條件.

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

我還在那里添加了可包含的行為.這使您可以從每個(gè)查詢中控制要與主要模型結(jié)果一起返回的關(guān)聯(lián)模型.它將默認(rèn)為全部，但是當(dāng)您只想要特定的東西和/或出于內(nèi)存原因時(shí)可能會(huì)很方便(如果您不限制它們或僅指定您想要的字段名稱，這些類型的查詢可能會(huì)很快破壞您的服務(wù)器內(nèi)存返回).

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

現(xiàn)在，當(dāng)您創(chuàng)建電子郵件模型時(shí)，我不建議通過再次將其鏈接回 ShootingPlacement 來進(jìn)一步復(fù)雜化這些糾纏模型.正如您所說，它還有一個(gè)指向 Person 模型的外鍵.因此，您可能希望對(duì) Person 模型執(zhí)行與上述完全相同的操作(當(dāng)然，更改條件以反映 Person 外鍵).這樣你的模型會(huì)更靈活一些；它仍然會(huì)加入到 ShootingPlacement 和 Person，并且還允許您在沒有其他關(guān)聯(lián)模型的情況下單獨(dú)查詢它.

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 模型關(guān)聯(lián)
• CakePHP 1.3 可包含行為

另見

• 這篇關(guān)于 Stack 的文章

這篇關(guān)于CakePHP 1.3 - where 子句中的未知列的文章就介紹到這了，希望我們推薦的答案對(duì)大家有所幫助，也希望大家多多支持html5模板網(wǎng)！

【網(wǎng)站聲明】本站部分內(nèi)容來源于互聯(lián)網(wǎng),旨在幫助大家更快的解決問題，如果有圖片或者內(nèi)容侵犯了您的權(quán)益，請(qǐng)聯(lián)系我們刪除處理，感謝您的支持！