ORM : Query with a join on the same table with a non-standard association


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.

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.

SELECT mt_ref.id, mt_ref.name, mt_ref.periodicity, 
    mt_inc.name, mt_inc.periodicity, o.name 
FROM maintenance_types mt_ref 
    LEFT JOIN maintenance_types mt_inc 
        ON (mt_inc.periodicity <= mt_ref.periodicity) 
    LEFT JOIN operations o ON (o.maintenance_type_id = mt_inc.id) 
WHERE mt_ref.id = 3 

I’ve tried to declare the association between MaintenanceTypes, but I can’t find a way to declare that the association is done on the periodicity field, and, extra points, not on a equality but on a “less or equal”.

To add extra difficulties, I use this query for a JQuery Datatables CakePHP plugin (https://github.com/allanmcarvalho/cakephp-datatables), so I can’t simply pass the raw SQL, and I must use the ORM…

I hope this is clear, and that someone could help me on this one !

Thanks a lot !

Found an answer on StackOverflow : https://stackoverflow.com/questions/61381433/cakephp-orm-query-with-a-join-on-the-same-table-with-a-non-standard-associatio/61384941#61384941