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
-~----------~----~----~----~------~----~------~--~---
