I ran across this problem a while ago.
Pretty much you have the following options:
1. set up your association with the condition
i.e. in your Company hasMany association set the condition
"Sector.sector_name = 'Biomass'"
2. bind your models at any point with this condition
at any point in your controller where you only want the companies that
include the biomass sector use the bindModel method with the condition
"Sector.sector_name = 'Biomass'"
3. write your own query
not the best way to do things but sometimes the only way.
----------
I would recommend 2. as I'm assuming you don't always want that
condition to exist but occasionally may need it. you can call the
bindModel at any point and overwrite all of the parameters set in your
initial association e.g. conditions.
cheers,
freedom
On 26/09/06, StinkyTofu <[EMAIL PROTECTED]> wrote:
>
> I am having trouble searching a joined table with the FindAll function.
>
> I have the following models:
>
> Company -> hasMany -> Sector
> Sector -> belongsTo -> Company
>
> The Sector table contains a field called sector_name. What I am trying
> to do is search the database and find all companies that contain a
> sector with the name Biomass'.
>
> I have the following code that performs the search:
>
> $results = $this->Company->findAll("Sector.sector_name = 'Biomass'",
> null, 'order by company_name', null, null, 1);
>
> However, running this code results in the following SQL:
>
> SELECT `Company`.`id`, `Company`.`name` FROM `companies` AS `Company`
> LEFT JOIN `financials` AS `Financial` ON `Financial`.`company_id` =
> `Company`.`id` WHERE `Sector`.`sector_name` = 'Biomass' ORDER BY
> `company_name` ASC;
>
> This SQL returns the error:
>
> Unknown table 'Sector' in where clause
>
> Anyone know what I may be doing wrong? Am I even taking the right
> approach here? Or should I use a custom query for something like this?
>
> Appreciate if someone can point me in the right direction.
>
> Thanks.
>
> Oh, and here is the Model code:
>
> class Company extends AppModel
> {
> var $name = 'Company';
> var $recursive = 2;
>
> var $uses = array('Util');
>
> var $hasOne = array ('Financial' =>
> array('className' => 'Financial',
> 'conditions' => '',
> 'order' => '',
> 'dependent' => true,
> 'foreignKey' => 'company_id'));
>
> var $hasMany = array ('Sector' =>
> array('className' => 'Sector',
> 'conditions' => '',
> 'order' => '',
> 'dependent' => true,
> 'exclusive' => true,
> 'finderSql' => '',
> 'foreignKey' => 'company_id'));
>
> }
>
>
> class Sector extends AppModel
> {
> var $name = 'Sector';
>
> //var $useTable = false; //Removes DB table link from this object, so
> you can now override the findAll methods
>
> var $belongsTo = array('Company' =>
> array('className' => 'Company',
> 'conditions' => '',
> 'order'=> '',
> 'dependent' => true,
> 'foreignKey' => 'company_id'));
>
> var $validate = array(
> 'title' => VALID_NOT_EMPTY,
> 'body' => VALID_NOT_EMPTY
> );
>
> }
>
>
> >
>
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups "Cake
PHP" group.
To post to this group, send email to [email protected]
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at http://groups.google.com/group/cake-php
-~----------~----~----~----~------~----~------~--~---
