Selecting pages based on descendant values

[Sinmok]

I'm trying to filter pages based on the values of one or more of their descendant pages.

 

Here's my tree

Project 1

    Tasks folder

        Task group 1

             Task 1 (assigned_to_user=user1)

             Task 2

Project 2

    Tasks folder

        Task group 1

             Task 1 (assigned_to_user=user2)

             Task 2

I'm trying to find all "projects" where one of the descending "tasks" assigned_to_user field = user1

 

So in the example above only "Project1" should return because project 2 has no tasks assigned_to_user=user1 

 

How can I do this as efficiently as possible considering there may be 100's of projects and several thousand tasks?

I hope this makes sense!

Thanks!

[ottogal]

Given your Page Tree structure, the following should be possible - assuming the Tasks have a common task_template with the field  assigned_to_user:

$matching_tasks = Spages->find("template=task_template, assigned_to_user=user1");
Smatching_projects = new PageArray();
foreach ($matching_tasks as $task) {
	$matching_projects->add($task->parent->parent->parent);
};

 

[ottogal]

If you have really many projects, to avoid loading them all into memory you should use findMany()  instead of find().

[ottogal]

Nope: That doesn't help since you are finding the tasks, not the projects.

So instead of creating a new PageArray you better create a normal PHP array $matching_project_IDs  and add the IDs of the matching projects:

$matching_project_IDs[] = $task->parent->parent->parent->id;