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
jeremybu...@me.com
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 cake-php@googlegroups.com
> To unsubscribe from this group, send email to
> cake-php+unsubscr...@googlegroups.com 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 cake-php@googlegroups.com
To unsubscribe from this group, send email to
cake-php+unsubscr...@googlegroups.com For more options, visit this group at
http://groups.google.com/group/cake-php?hl=en
