Hi Brian, I hope I can help.
On Mar 3, 2:53 pm, bcreeve <bcre...@gmail.com> wrote: > 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. Based on your description, I think you misunderstand the concept of coupling. "Low coupling refers to a relationship in which one module interacts with another module through a stable interface and does not need to be concerned with the other module's internal implementation" (from http://en.wikipedia.org/wiki/Coupling_(computer_science) ). Particularly in terms of record relationships in third normal form, the concept of coupling almost doesn't even apply. > 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. In my experience, I'd say you're better off explaining your actual problem, rather than a contrived one. Your explanation would lead someone to reasonably conclude that you're talking about a model (i.e. Person) joined on itself in a hasMany/belongsTo relationship, however, the way you're approaching the explanation seems to contradict this. Your example is ambiguous. > 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). This is called de-normalization, and is a very common thing to want to do, for many reasons, including but not limited to performance improvements and reducing code complexity. You can see an example implemented in the CakePHP core in the form of counter caches for hasMany/belongsTo relationships: http://book.cakephp.org/view/490/counterCache-Cache-your-count > For my needs, the above has several limitations: > 1. I cannot query against any Child columns or these calculated > columns. This seems like an artificial constraint. Why not query the Child object directly? > 2. I cannot sort on said columns. See above. > 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. If you query the Child object directly, Parent will be attached via a LEFT JOIN, and everything will happen in one query. Also, I'm not sure what you mean about "pseudo" columns. Why not just actually create the columns you mentioned above, and use them to cache count values? > 4. Lastly, I need to paginate filtered and/or sorted results, which > just seems to add another layer of complexity. See the solution I mentioned immediately above. The default core pagination would apply perfectly in that scenario. > 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 think I have explained an appropriate solution above. Please post a follow-up with any relevant clarifying detail if this will not work for your situation. > 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? As long as you properly alias all the columns in the view (search this list for examples), yes, it will work the same as normal Cake query results. > And then for pagination, I should be able to do something like > described inhttp://book.cakephp.org/view/249/Custom-Query-Pagination, > or no? Sure. Hope that helps. --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "CakePHP" group. To post to this group, send email to cake-php@googlegroups.com To unsubscribe from this group, send email to cake-php+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/cake-php?hl=en -~----------~----~----~----~------~----~------~--~---
