Re: Find(all) query in multiple table

The issue is Staff is not being joined, are the relationships correct in the models ?

Also 'Event.id = Staff.user_id' I think this likely 'Event.user_id = Staff.user_id' ….

Also since you looking for "events" you could start the query from events not from users…

Andras Kende
http://www.kende.com

On Jan 21, 2012, at 9:08 PM, Ivan wrote:

> Hi,
> Maybe someone can help me with this...
> The tables are linked this way:
> http://dl.dropbox.com/u/52896949/Screen%20Shot%202012-01-22%20at%201.03.03%20AM.png
> What i need is:
> Find all Events that a user is linked with.
> Witch means: that a user is "owner" (the Events.user_id) or that the
> user is staff of (Table Staffs).
> So, what i have made is:
> ----
> public function getEventsOf($User_id){
>
> $conditions = array(
> 'Event.status' => 'active',
> 'AND' => array(
> 'OR' => array(
> 'Event.user_id' => $User_id,
> 'OR'=>array(
> 'AND' => array(
> 'Event.id = Staff.user_id',
> 'Staff.user_id' => $User_id,
> ),
> ),
> ),
> ),
> );
> $users = $this->find('all', array(
> 'conditions'=> $conditions,
> 'recursive' => 0) );
>
> return $users;
> }
>
> -----
> But a error shows up:
> -----
> Error: SQLSTATE[42S22]: Column not found: 1054 Unknown column
> 'Staff.user_id' in 'where clause'
> SQL Query: SELECT `Event`.`id`, `Event`.`user_id`, `Event`.`title`,
> `Event`.`description`, `Event`.`country`, `Event`.`state`,
> `Event`.`city`, `Event`.`date_start`, `Event`.`date_end`,
> `Event`.`pin`, `Event`.`pros_cons`, `Event`.`status`,
> `Event`.`created`, `Event`.`modified`, `User`.`id`,
> `User`.`first_name`, `User`.`last_name`, `User`.`email`,
> `User`.`password`, `User`.`username`, `User`.`country`,
> `User`.`state`, `User`.`status`, `User`.`created`, `User`.`modified`
> FROM `events` AS `Event` LEFT JOIN `users` AS `User` ON
> (`Event`.`user_id` = `User`.`id`) WHERE `Event`.`status` = 'active'
> AND ((`Event`.`user_id` = 12) OR (((`Event`.`id` = `Staff`.`user_id`)
> AND (`Staff`.`user_id` = 12))))
> ----
>
> What do i do?
> Thanks...
> Ivan
>
> --
> Our newest site for the community: CakePHP Video Tutorials http://tv.cakephp.org
> Check out the new CakePHP Questions site http://ask.cakephp.org and help others with their CakePHP related questions.
>
>
> 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

--
Our newest site for the community: CakePHP Video Tutorials http://tv.cakephp.org
Check out the new CakePHP Questions site http://ask.cakephp.org and help others with their CakePHP related questions.

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