Hey Nate. Thanks for the feedback but I'm not sure it provided much help for me. Hopefully it will provide some insight for some others.
By "tightly coupled" I simply meant that I pretty much always want to look at data from both tables together. I didn't realize the term was conflicting with an existing notion of coupling. In hindsight I suppose the contrived example is insufficient to explain reality. I have Voicemail hasMany Transcription (and Transcription belongsTo Voicemail). When I mentioned a MySQL view to solve the problem of calculated columns, you replied: > 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. For what it's worth, creating a MySQL View to join the two and include calculated columns is not de-normalization. Each table is still as normalized as it is without the view. Using a view is really just a valid choice to move some core business logic to the database for performance and complexity reasons. >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 I currently use counter cache where appropriate, however AFAIK I can't have an auto-magic counter cache while specifying child conditions. For instance, at the Voicemail level I need to know how many Transcriptions are transcribed and how many are untranscribed. Also, the the voicemail level, I want a boolean column as a flag to tell me if any transcriptions are transcribed but blank. In my Oracle and MS SQL experience, no question this stuff would be best calculated a view or pipeline function. Cake seems to want things done in Cake, the Cake way. I buy into this only as long as it is possible or practical to do so within the framework. So I started adding these calculated columns in the afterFind method of the Voicemail model. Now they are always available when I find a Voicemail (these were referred to as pseudo columns or pseudo fields in other articles or posts). Problem is, these columns end up being a separate SELECT COUNT statement to the DB, which is not going to scale well in terms of performance. > > 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? The context is not at the child level. I'm trying to search and sort Voicemails (in the Voicemail index) based on the status of its collective child Transcriptions. In my opinion, it is not appropriate to be querying about Voicemails at the Transcriptions level. Also, it's not trivial or appropriate to be asking about information about sibling Transcriptions that are related by voicemail_id at the Transcription level. In other words, I don't want to ask the Transcriptions if all Transcriptions for a particular Voicemail are adequately transcribed. I should be able to ask a Voicemail about its children Transcriptions. > > 2. I cannot sort on said columns. > > See above. Same as #1 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? See #1 > > 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. Thanks for the feedback but I'm not sure we were on the same page initially. Does my response give you any more clarification? brian --~--~---------~--~----~------------~-------~--~----~ 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 -~----------~----~----~----~------~----~------~--~---
