[cakephp] problem with find query

Question

Hi

I have a problem with find query:

$userInProjects = $this->Timesheet->RegularPost->UserInProject->find('all', array('conditions' => array('UserInProject.user_id' => $id)));

The result array:

Array
(
[0] => Array
   (
       [UserInProject] => Array
           (
               [id] => 11
               [project_id] => 3
               [position_id] => 1
               [user_id] => 15
           )

       [Project] => Array
           (
               [id] => 3
               [short_name] => proj1
               [full_name] => project 1
               [start_date] => 2010-01-01
               [end_date] => 2010-05-01
               [agreement_number] => 12/34U/23
               [active] => 1
               [user_id] => 1
           )

       [Position] => Array
           (
               [id] => 1
               [name] => some_name
           )

       [User] => Array
           (
               [id] => 15
               [username] => foo
               [first_name] =>
               [last_name] =>
               [email] => [email protected]
               [active] => 1
               [created] =>
               [modified] =>
           )

       [RegularPost] => Array
           (
               [0] => Array
                   (
                       [id] => 792
                       [date] => 2010-01-01
                       [size] => 0.20000
                       [users_in_project_id] => 11
                   )

               [1] => Array
                   (
                       [id] => 793
                       [date] => 2010-02-01
                       [size] => 0.20000
                       [users_in_project_id] => 11
                   )

               ( and many more ...)

           )

   )

[1] => Array
   (
       [UserInProject] => Array
           (
               [id] => 20
               [project_id] => 3
               [position_id] => 2
               [user_id] => 15
           )

       [Project] => Array
           (
               [id] => 3
               [short_name] => proj1    
               [full_name] => project 1
               [start_date] => 2010-01-01
               [end_date] => 2010-05-01
               [agreement_number] => 12/34U/23
               [active] => 1
               [user_id] => 1
           )

       [Position] => Array
           (
               [id] => 2
               [name] => some_name2
           )

       [User] => Array
           (
               [id] => 15
               [username] => foo
               [first_name] =>
               [last_name] =>
               [email] => [email protected]
               [active] => 1
               [created] =>
               [modified] =>
           )

       [RegularPost] => Array
           (
               [0] => Array
                   (
                       [id] => 836
                       [date] => 2010-01-01
                       [size] => 0.2
                       [users_in_project_id] => 20
                   )

               [1] => Array
                   (
                       [id] => 837
                       [date] => 2010-02-01
                       [size] => 0.3
                       [users_in_project_id] => 20
                   )

               [2] => Array
                   (
                       [id] => 838
                       [date] => 2010-03-01
                       [size] => 0.3
                       [users_in_project_id] => 20
                   )

               ( and many more ...)

           )
   )
)

What I want to achive is the array like above but RegularPost with [date] => 2010-02-01 only. Is there any way to pass the date '2010-02-01' to the RegularPost in this query?

This query doesn't work:

$userInProjects = $this->Timesheet->RegularPost->UserInProject->find('all', array('conditions' => array('UserInProject.user_id' => $id, 'RegularPost.date' => '2010-02-01')));

1054: Unknown column 'RegularPost.date' in 'where clause'

Please help. :)

Answer 1

@Rob Wilkerson

UserInProject hasMany RegularPost and RegularPost belongsTo UserInProject

And yes, I have turned debug level 2.

Here are the SQL queries:

SELECT `UserInProject`.`id`, `UserInProject`.`project_id`, `UserInProject`.`position_id`, `UserInProject`.`user_id`, `Project`.`id`, `Project`.`short_name`, `Project`.`full_name`, `Project`.`start_date`, `Project`.`end_date`, `Project`.`agreement_number`, `Project`.`active`, `Project`.`user_id`, `Position`.`id`, `Position`.`name`, `User`.`id`, `User`.`username`, `User`.`first_name`, `User`.`last_name`, `User`.`email`, `User`.`password`, `User`.`active`, `User`.`created`, `User`.`modified`, (CONCAT(`User`.`last_name`, " ", `User`.`first_name`, " (", `User`.`username` , ")")) AS `User__full_name` FROM `user_in_projects` AS `UserInProject` LEFT JOIN `projects` AS `Project` ON (`UserInProject`.`project_id` = `Project`.`id`) LEFT JOIN `positions` AS `Position` ON (`UserInProject`.`position_id` = `Position`.`id`) LEFT JOIN `users` AS `User` ON (`UserInProject`.`user_id` = `User`.`id`) WHERE `UserInProject`.`user_id` = 15 

(this query is made by cakephp automatically):

SELECT `RegularPost`.`id`, `RegularPost`.`date`, `RegularPost`.`size`, `RegularPost`.`users_in_project_id` FROM `regular_posts` AS `RegularPost` WHERE `RegularPost`.`users_in_project_id` IN (11, 20) 

So, is it possible to pass the date '2010-02-01' to second query?

Answer 2

You need to use the Containable behaviour:

$userInProjects = $this->Timesheet->RegularPost->UserInProject->find('all', array(
    'conditions' => array('UserInProject.user_id' => $id),
    'contain' => array(
        'User', 'Position', 'Project',
        'RegularPost' => array(
            'conditions'=> array( 'RegularPost.date' => '2010-02-01' )
         )
    )
));

This query tells Cake's ORM to retrieve all UserInProject records with the id property you supplied in $id, as well as all associated User, Position and Project records. Finally, it tells Cake's ORM to retrieve all RegularPost records associated with the retrieved UserInProject records that have a date property equal to 2010-02-01.