Thanks to those who tried to help.
I was unable to use "find" to retrieve the data based on the HABTM
relationship. I ended up doing manually:
<----------------------
//lookup User based on email
$user = $this->User->find('first',
array(
'conditions'=>array('email'=>$this->data['User']['email']),
'fields'=>array('id')
)
);
//lookup SiteUsers associated with the selected User
$site_users = $this->User->SiteUser->find('list',
array(
'conditions'=>array('user_id'=>$user['User']['id']),
'fields'=>array('site_id')
)
);
//compile an array of Sites associated with the User (based on
SiteUser)
$sites = array();
foreach ( $site_users as $value){
$site = $this->Site->find('first',
array(
'conditions'=>array('Site.id'=>$value['site_id']),
'fields'=>array('id','title')
)
);
$sites[$site['Site']['id']]=$site['Site']['title'];
}
$this->set('options', $sites);
---------------------->
I would be interested in knowing why it didn't work, if anyone knows
why.
Cheers Paul.
On Nov 25, 7:06 pm, AD7six <[EMAIL PROTECTED]> wrote:
> On Nov 25, 1:26 am, pkclarke <[EMAIL PROTECTED]> wrote:
>
>
>
> > I'm a noob to CakePHP and have an issue with "find" returning results
> > from tables with a HABTM relationship.
>
> > I have 2 tables that have a HABTM relationship:
>
> > - Site.id
> > - Site.title
>
> > - User.id
> > - User.name
>
> > The HABTM join table is:
> > - SiteUser.id
> > - SiteUser.site_id
> > - SiteUser.user_id
>
> > The Site relationship is defined as follows:
> > var $hasAndBelongsToMany = array(
> > 'User' =>
> > array(
> > 'className'=> 'User',
> > 'joinTable'=> 'site_users',
> > 'foreignKey'=> 'site_id',
> > 'associationForeignKey'=> 'user_id',
> > 'unique'=> true
> > )
> > );
>
> > I am trying to return Sites related to a given User using "find", as
> > follows:
> > $options = $this->User->Site->find('list',
> > array(
> > 'fields'=>array('Site.id', 'Site.title'),
> > 'conditions'=>array('User.id'=>'7')
> > )
> > );
>
> > However, I get the following error:
> > Warning (512): SQL Error: 1054: Unknown column 'User.id' in 'where
> > clause'
>
> > The SQL returned shows the HABTM relationship doesn't seem to be
> > working:
> > Query: SELECT `Site`.`id`, `Site`.`title` FROM `sites` AS `Site`
> > WHERE `User`.`id` = 7
>
> > Can anyone shed some light on what I'm doing wrong?
>
> See the
> examples:http://book.cakephp.org/revisions/results/query:habtm/collection:2/la...
>
> Cheers,
>
> AD
--~--~---------~--~----~------------~-------~--~----~
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
-~----------~----~----~----~------~----~------~--~---
