在CakePHP中，如何在相关表上根据条件选择记录

Question

另一个新手问题-使用CakePHP的1.2.1.8004版，我想...

我有3个表，broker (B)，quote_site (QS)和broker_quote_site (BQS)将它们链接在一起。

B有许多BQS (它属于B) QS有许多BQS (它属于QS)

我正在尝试检索报价站点，这些站点链接到特定的代理，但CakePHP没有在幕后连接到表。

下面是我的问题：

    $quote_sites = $this->QuoteSite->find('all', array(
        'conditions' => array(
            'Broker.company_id' => $company_id,
            'BrokerQuoteSite.is_active' => true
        ),
        'contain' => array(
            'BrokerQuoteSite' => array(
                'Broker'
            )
        )
    ));

以下是相关的模型：

<?php
class QuoteSite extends AppModel
{
    var $name = 'QuoteSite';
    //$validate set in __construct for multi-language support
    //The Associations below have been created with all possible keys, those that are not needed can be removed
    var $hasMany = array(
        'BrokerQuoteSite' => array(
            'className' => 'BrokerQuoteSite',
            'foreignKey' => 'quote_site_id',
            'dependent' => false,
            'conditions' => '',
            'fields' => '',
            'order' => '',
            'limit' => '',
            'offset' => '',
            'exclusive' => '',
            'finderQuery' => '',
            'counterQuery' => ''
        )
    );
}
?>

经纪人：

<?php
class Broker extends AppModel
{
    var $name = 'Broker';
    //$validate set in __construct for multi-language support
    //The Associations below have been created with all possible keys, those that are not needed can be removed
    var $hasMany = array(
        'BrokerQuoteSite' => array(
            'className' => 'BrokerQuoteSite',
            'foreignKey' => 'broker_id',
            'dependent' => false,
            'conditions' => '',
            'fields' => '',
            'order' => '',
            'limit' => '',
            'offset' => '',
            'exclusive' => '',
            'finderQuery' => '',
            'counterQuery' => ''
        )
    );
}
?>

最后一个：

<?php
class BrokerQuoteSite extends AppModel
{
    var $name = 'BrokerQuoteSite';
    //$validate set in __construct for multi-language support
    //The Associations below have been created with all possible keys, those that are not needed can be removed
    var $belongsTo = array(
        'Broker' => array(
            'className' => 'Broker',
            'foreignKey' => 'broker_id',
            'conditions' => '',
            'fields' => '',
            'order' => '',
        ) ,
        'QuoteSite' => array(
            'className' => 'QuoteSite',
            'foreignKey' => 'quote_site_id',
            'conditions' => '',
            'fields' => '',
            'order' => '',
        )
    );
}
?>

提前感谢您的任何提示/技巧。

Answer 1

Chris，为什么你不将Broker定义为HABTM关系，那么一个简单的find就会检索到想要的结果？

class Broker extends AppModel {
    var $name = 'Broker';   
    var $hasAndBelongsToMany = array(
        'QuoteSite' =>
            array(
                'className'              => 'QuoteSite',
                'joinTable'              => 'broker_quote_sites',
                'foreignKey'             => 'broker_id',
                'associationForeignKey'  => 'quote_site_id'                    
            )
    );
}

http://book.cakephp.org/view/1044/hasAndBelongsToMany-HABTM

Answer 2

问得好，我想把它分成两步

$this->Broke = ClassRegistry::init("Broke");
$brokeids = $this->Broke->find("list",array("conditions"=>array('Broker.company_id' => $company_id)));     //get all B ids

$this->QuoteSite->Behaviors->attach('Containable');  //use containable behavior
$quote_sites = $this->QuoteSite->find('all',array(
                                                     'contain'=>array(
                                           'BrokerQuoteSite'=>array(
              'conditions'=>array(
                                  "BrokerQuoteSite.broke_id"=>$brokeids,
                                  "BrokerQuoteSite.is_active" => true
                                 )
                                                                   )
                                                                      )

                                                  )

                                      );     

代码还没有经过测试，可能会有一些语法错误there.Hope它会有所帮助。

更新

$this->Broke = ClassRegistry::init("Broke");
$this->Broke->recursive=2;
$brokes = $this->Broke->find("all",array("conditions"=>array("Broke.company_id"=>$comany_id)));

如果您使用debug($brokes); .What查看结果，则可以找到您需要的QS信息。您需要做的是从数组中提取它们。

干杯。