Editing associated data base on its next level associated data?

Need Help
1

My application has models structure like this:
Jobs hasMany Tasks, each Tasks in turn belongToMany Users (1 task can be done by more than 1 users).
Now, I want to retrieve all the Tasks belong to a certain Jobs which related to the current logged in users only.
E.g.: The given Jobs may has 10 Task, and 5 tasks are related to current logged in users. I just want to display only those 5 tasks and edit their statuses.

Should I edit tasks in Tasks controllers or as associated data in Jobs controller?
How would I build find query to filter Tasks which related to current logged in user?

Thank you very much.

2

I tried below code in Jobs controller, but it does not work

$job = $this->Jobs->find('all', [
            'conditions' => ['Jobs.id' => $id],
            'contain' => ['Tasks.Users'],
            'matching' => ['Tasks.Users' => function ($q) use ($curUser){
                return $q->where(['Users.id' => $curUser->id]);}]
        ])->first();

The code simply list get the Job, contain all Tasks, and in each Tasks, only get the Users if has the same ID , or empty Users

I don’t understand why is that.

3

Ah, I figure out what is wrong with my code.
I can not use array format for matching, need to use as function format like below, and also n6eed to be done in Tasks Controller

$tasks= $this->Tasks->find('all', ['conditions' => ['job_id' => $id]])
            ->contain('Users')
            ->matching('Users', function ($q) use ($curUser){ return $q->where(['Users.id' => $curUser->id]); } )
            ->all();
4

Although you found your solution I am just wondering what’s happening in the guts of CakePHP to make this work. It looks like its launching a query per user, so potientally it could be executing thousands of queries for what could be done with a single SQL.

If you know the dataset is limited (like for 30 employees) its not too heavy I suppose - but perhaps someone could clarify? Or maybe have a cleaner solution? I appologise if I have misunderstood the solution (or the effectiveness of Cake’s internal caching).

5

My understanding is that matching causes a join to happen, and adds the specified condition, so it’s all a single query. Seems like it might be slightly more “natural” here to get the current user, containing Tasks, with a query on the contain to only match the specified job id, but that’s another story.

6

Does join type affect this?

7

The Query log is at below:

SELECT 
  HrPts.id AS HrPts__id, 
  HrPts.name AS HrPts__name, 
  
  HrPtTasks.id AS HrPtTasks__id, 
  HrPtTasks.description AS HrPtTasks__description, 
  HrPtTasks.hr_pt_id AS HrPtTasks__hr_pt_id, 
  
  HrPtTasksUsers.id AS HrPtTasksUsers__id, 
  HrPtTasksUsers.user_id AS HrPtTasksUsers__user_id, 
  HrPtTasksUsers.hr_pt_task_id AS HrPtTasksUsers__hr_pt_task_id, 

  Users.id AS Users__id, 
  Users.username AS Users__username, 
  
FROM 
  hr_pts HrPts 
  INNER JOIN hr_pt_tasks HrPtTasks ON HrPts.id = (HrPtTasks.hr_pt_id) 
  INNER JOIN hr_pt_tasks_users HrPtTasksUsers ON HrPtTasks.id = (HrPtTasksUsers.hr_pt_task_id) 
  INNER JOIN users Users ON (
    Users.id = 154 
    AND Users.id = (HrPtTasksUsers.user_id)
  ) 
WHERE 
  HrPts.id = 26

Please note that I changed table names for you to easily get the meaning
HrPts => Jobs
HrPtTasks => Tasks
Users

If I run that SQL statement in PHPMyAdmin, it also give 3 row of results, instead of 1 Jobs with 3 Tasks. I think CakePHP need to have subquery in some ways to retrieve the contains