The secret lies in setting up your models correctly. You also will find life
easier if you use the field name 'name' instead of 'label'. Doing this means
that you can easily create drop downs populated with 'id' and 'name' where
'name' is the displayed value. This is the default Cake convention.
I'd say your models ought to look something like this:
Item:
var $belongsTo = array(
'City' => array(
'className' => 'City',
'foreignKey' => 'city_id'
),
'Category' => array(
'className' => 'Category',
'foreignKey' => 'category_id'
)
);
City:
var $hasMany = array(
'Item' => array(
'className' => 'Item',
'foreignKey' => 'city_id'
)
);
Category:
var $hasMany = array(
'Item' => array(
'className' => 'Item',
'foreignKey' => 'category_id'
)
);
This set ups the joins between your tables.
I'd also strongly recommend using recursion and the containable behaviour as it
gives you tremendous control over exactly what gets returned. I wouldn't leave
home without it now, and place it in my AppModel:
var $actsAs = array('Containable');
var $recursive = -1;
Then your SQL statement (when called from within the items controller) becomes
a find:
$variable = $this->Item->find(
'all',
array(
'fields' => array(
'City.id', 'City.name'
),
'contain' => array(
'Category' => array(
'Category.label' => 'Doctors' // mostly going
to be a variable, e.g. $categoryLabel
),
'City' => array(
'City.label' => 'Boston' // mostly going to be
a variable, e.g. $cityLabel
)
)
)
);
I haven't tested that - just typed it here - but it ought to work.
Jeremy Burns
[email protected]
On 18 Mar 2010, at 01:27, Andrew wrote:
> Hello,
>
> Thank you for your time, I am a new user of CakePHP. I recently found
> CakePHP and am still trying to get used to the MVC architecture. (By
> the way, it's brilliant). However I'm still getting used to
> reformatting my SQL into cakephp.
>
> My question is as follows. I have an SQL statement that (simplified)
> is like the following:
>
> select i.name
> from item i, city c, category ca
> where ca.id = i.category_id
> and c.id = i.city_id
> and ca.label = "Doctors"
> and c.label = "Boston"
>
>
> I have been able to create step one --
>
> $this->set('items', $this->Item->findAllByCategoryId('1'));
>
> And now I'm totally stuck, as this is the first model I've created. I
> had a look through the model documentation but couldn't see anywhere
> that dealt with SQL from two tables :-(
>
> Thanks
> Andrew
>
> Check out the new CakePHP Questions site http://cakeqs.org and help others
> with their CakePHP related questions.
>
> You received this message because you are subscribed to the Google Groups
> "CakePHP" 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?hl=en
Check out the new CakePHP Questions site http://cakeqs.org and help others with
their CakePHP related questions.
You received this message because you are subscribed to the Google Groups
"CakePHP" 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?hl=en
