On Mar 4, 6:06 pm, bcreeve <bcre...@gmail.com> wrote:
> 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 aMySQL viewto 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 aMySQL Viewto join the two and include
> calculated columns is not de-normalization.
You're quoting out of context/sequence and possibly missing the point.
> 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.
indeedy. To all intents and purposes a db-view is just a table to
cake.
> >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.
I don't follow your example but if you did de-normalize (which means
literally creating a field for each of your counts) you can use
counterCache and counterScope to update the values as appropriate. You
could also do it directly with group by and agregate functions or if
you use a view, no need for countercache but you'll probably find that
given views in mysql are typlically un-optimized that only
superficially solves your problem.
> 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
That's typically a mistake.
> 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.
That's the problem with contrived examples.
> 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.
It looks like your ignoring the answer that's already been given (de-
normalize).
>
> > > 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?
Maybe. In what way would using a view (if you choose) or using
counterCache fields for each of your agregate fields, or a stock left
join, group by and agregate function, NOT solve your question?
hth,
AD
--~--~---------~--~----~------------~-------~--~----~
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
-~----------~----~----~----~------~----~------~--~---
