0

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. :)

Improve this question
2
  • How is RegularPost associated? Is it a hasMany relationship with UserInProject or hasAndBelongsToMany? Also, have you turned on debug to at least level 2? HABTM is pulled after the fact, so that could be the cause of your issue. Commented May 13, 2010 at 12:07
  • If you get that SQL error you should also get the particular query itself that fails. You could have a look at the column names in the actual query and post the full query here as well. Commented May 13, 2010 at 12:33

1 Answer 1

Reset to default
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.

Improve this answer
Sign up to request clarification or add additional context in comments.

3 Comments

Thank you for your answer, but it doesn't work. I have added var $actsAs = array('Containable'); to my model and I used your code. It still return the array from my question with all RegularPost. The date isn't passed to the secondary query.
Ok, it works! ;) I'm stupid. I have added the code var $actsAs = array('Containable'); to Timesheet model instead of to UserInProject model. Now it works like a charm. Thank you Daniel very, very much! You saved my day.
Glad it worked out in the end! Containable is such a useful behaviour, you may want to drop it into your AppModel definition. I don't know what the performance implications of that is, versus including it per-model, but it's something to consider. =)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.