Hi,
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 
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 !