CakePHP 4: Filtering a find() result in a command object

Need Help
1

I’m building a command object that will run as a cronjob. The object will email anyone listed as the Responsible Engineer when they have work orders that haven’t been completed by the completion date. Here is the code for that:

$wos = $this->WorkAuthorizations->find('all', [
        'contain' => [
            'Projects' => [
                'OpsLead',
                'ChiefEngineer'
            ],
            'RespEngineers',
            'TechSupervisors',
            'PerfPeriod',
        ],
    ])->where(
        'est_completion_date = DATE_ADD(CURDATE(), INTERVAL -1 DAY)
        AND is_recurring = 0 AND status_id = 9'
    );

This gets me the raw result set. Anything with, say, a due date of 7/20/2021 that is in status 9 will appear if today’s date is 7/21/2021. Now, what I need to do is filter this result set per responsible engineer so that they receive an email ONLY for the work orders they’re tied to. RespEngineers in the contain statement contains that information, pulled from the Users class via the foreign key resp_engineer_id. So I need to roll up anything where WorkAuthorizations->resp_engineer_id = WorkAuthorizations->resp_engineer->id.

Meaning, if my result set looks like this:

id        resp_engineer_id               resp_engineer.id
--       ------------------------       -----------------------
1        2                                  2
2        3                                  3
3        2                                  2
4        3                                  3

How do I make it so that 2 only sees ID’s 1 and 3 in their email, and 3 only sees ID’s 2 and 4 in theirs?

2

You could use groupBy functionality of collections, or you could use a custom loop, but I’d say the way to go is actually to restructure your query such that you find all RespEngineers matching WorkAuthorizations. Isn’t that a more natural expression of the solution?

3

I ended up wrapping my statement in a foreach() and looped through my users who have work orders in the system, using their IDs to match up with the resp_engineer_id in the document. Working great now.

4

I still think changing your query would provide a more elegant solution, but sometimes elegance isn’t worth it.