Re: "find" using HABTM relationship

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 <andydawso...@gmail.com> wrote:
> On Nov 25, 1:26 am, pkclarke <paul.k.cla...@gmail.com> 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 cake-php@googlegroups.com
To unsubscribe from this group, send email to cake-php+unsubscribe@googlegroups.com
For more options, visit this group at http://groups.google.com/group/cake-php?hl=en
-~----------~----~----~----~------~----~------~--~---