Most often the way Cake handles relationships is very efficient for getting new development off the ground. However, I recently have hit a wall when it comes to handling two or more tables that are tightly coupled. By "tightly coupled" I mean the data from multiple tables makes most sense when looked at in conjunction.
In the interest of simplicity, let's say I have Parent that hasMany Child (and Child belongs to Parent). I often want to view, search and sort using anything like the parent's last name, number of children, number of male children, are any children under 18, etc. It's conceivable that everywhere I consider a Parent, I also want to have access to all the data I mentioned in the paragraph above. So, I add some pseudo-columns in the Parent model called child_count, male_child_count, and has_children_under_18. Great, now I have this data available every time I ask for Parent(s). For my needs, the above has several limitations: 1. I cannot query against any Child columns or these calculated columns. 2. I cannot sort on said columns. 3. Cake gives each Child get its own SELECT via hasMany, and each pseudo column for each parent gets its own SELECT, which likely won't scale with respect to performance. 4. Lastly, I need to paginate filtered and/or sorted results, which just seems to add another layer of complexity. Basically, I am wondering if there is a native Cake 1.2 way of handling this. If this is a case where the framework doesn't fit the need, I would like a sanity check to make sure the following is the most Cake-like way to do it. I am considering writing a MySQL view called something like parents_children. It will join parents with children and calculate/ expose the necessary columns. Then I will bake a model for this MySQL view and make my ParentsController use the freshly baked ParentChild model. My index can programatically build WHERE and ORDER BY clauses and use $this->ParentChild->query(...). All my read operations can then use $this->ParentChild->find(...). From what I understand, as long as I alias my MySQL view columns like Model.column, I should still get a nice associative array like $parentChild['Parent'] ['last_name'] from query and find, right? And then for pagination, I should be able to do something like described in http://book.cakephp.org/view/249/Custom-Query-Pagination, or no? Again, just looking for a sanity check or if there are any other suggestions from people who have handled similar problems in Cake. Thanks a lot! brian --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "CakePHP" group. To post to this group, send email to [email protected] To unsubscribe from this group, send email to [email protected] For more options, visit this group at http://groups.google.com/group/cake-php?hl=en -~----------~----~----~----~------~----~------~--~---
