cakephp-datatable | CakePHP Component for interoperability between CakePHP | Plugin library

I have a very particular use case, and i can't find a clean solution with the ORM. I've searched a lot, and maybe my database model is not correct, I'm not sure.

I use CakePHP 3.8.11.

So, I have a table "MaintenanceTypes" with 3 important fields : id, name, and periodicity. Periodicity (in days) means "this maintenance is to be done every (for instance) 30 days".

Periodicity are like 7 (week), 30 (month), 90 (trimester) and so on.

I also have a table "Operations", they are little unit tests that belongs to a "MaintenanceType" (fields are id, name, maintenance_type_id).

What is special in this case, is that, as a business rule, Operations belonging to a MaintenanceType with a periodicity of 7 days is "included" in every MaintenanceType with a greater periodicity; that means that every trimester, you should do every Operations associated directly to the trimester, but also every Operations associated with the month, and the week, etc.

In raw SQL it's trivial :slight_smile:

mt_ref is the reference MaintenanceType, mt_inc are the included MaintenanceTypes (with a lesser periodicity) and finally, every Operations belonging to any of the MaintenanceTypes found.