With CakePHP, How to select records based on conditions on related tables

Another noob question - using v1.2.1.8004 of CakePHP, I think...

I have 3 tables, broker (B), quote_site (QS) and broker_quo开发者_C百科te_site (BQS) that links them together.

B has many BQS (it belongs to B) QS has many BQS (it belongs to QS)

I am trying to retrieve quote sites, that are linked to a specific broker, but CakePHP is not doing the joins to the tables behind the scenes.

Here is my query:

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

Here are the related models:

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

Broker:

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

And the last one:

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

Thanks in advance for any tips/tricks.

Chris why don't you define Broker as a HABTM relationship then a simple find will retrive the desired results?

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

Nice question and what I thought is to make it two steps

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

                                                  )

                                      );     

The code is NOT tested yet,maybe some syntax mistakes out there.Hope it helps.

update

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

the QS information you need shall be found if you view the result with debug($brokes); .What you need to do is extracting them from the array.

Cheers.