Re: How to Handle Tightly Coupled Database Tables?
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 -~--~~~~--~~--~--~---
Re: How to Handle Tightly Coupled Database Tables?
On Mar 4, 1:01 am, Miles J mileswjohn...@gmail.com wrote: Have you tried paginating/searching from the actual HABTM model? In this case your model would be ChildParents. Miles: Thanks, but I have a hasMany/belongsTo relationship, not HABTM, so there is no relationship table. --~--~-~--~~~---~--~~ 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 -~--~~~~--~~--~--~---
Re: How to Handle Tightly Coupled Database Tables?
On Mar 4, 2:05 pm, AD7six andydawso...@gmail.com wrote: 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. You're right. My mistake. However, my point is still valid as I believe Nate originally incorrectly considered calculating pseudo- columns in the model s afterFind method de-normalization. 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? Everything you mention is sufficient, except using counterCache. I was trying to get a sanity check to make sure I wasn't bypassing some already existing piece of Cake. Since it seems the community is divided when it comes to using views/functions in the database or using a custom query (really the same thing) I wanted to see if there really is a Cake way to do what I want. De-normalizing would certainly allow me to search and sort as I described. I know counterCache can be used to populate a voicemails.transcription_count column, but I don't know that I can use it to populate something like voicemails.transcribed_transcription_count or a boolean voicemails.has_blank_transcriptions, can I? Maybe that is the purpose of counterScope, which does not appear to be documented at book.cakephp.org. I'll check into Cake's core and unit tests to see if it would do the trick. De-normalizing also does not support a calculated column that depends on a point in time. For instance if a transcription can be locked for 15 minutes, and that 15 minute window passes, a voicemails.locked_transcription_count column is not instantly (or ever) updated. The best you could do is set up a cron job to manage these, which is not clean. A view with a calculated column (like DATEDIFF with SYSDATE) handles all of this by trading off a bit of read-time performance. I think this trade off very much outweighs the cost of development complexity to manage de-normalizing data. Thanks all for the help. But given my situation, and the lack of another clear solution that will solve every challenge I've noted, I think creating a view is going to be the best option. --~--~-~--~~~---~--~~ 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 -~--~~~~--~~--~--~---
How to Handle Tightly Coupled Database Tables?
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 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 -~--~~~~--~~--~--~---
Re: Debug Comment Data in JSON Results
What I do is parse .json extensions and return the JSON with
Configure::write('debug', 0); like brian stated.
If I want to see what errors I'm getting when things bomb, I can hit
the same URL without the extension to return the data as HTML in the
default layout while respecting the environment's debug mode.
On Mar 3, 2:43 pm, brian bally.z...@gmail.com wrote:
On Tue, Mar 3, 2009 at 2:09 PM, Alex alex.gibb...@gmail.com wrote:
I'm using the javascript-object() method to return JSON data for XHR
requests. All works great when the cake debug level is set to 0, but
when higher will append an html style comment to my JSON data like so:
!-- 0.5613s --, indicating the response time of the request. This
prohibits the JSON data from being evaluated. Is there anyway to keep
a non-zero debug level and keep cake from appending this value?
Do you mean, so that you can see debugging info aside from the XHR request?
Configure::write('debug', 0);
... just before you send the JSON data. This way, you can see
debugging info elsewhere. There's no practical way to output debugging
info *with* your JSON data, as you've already seen.
--~--~-~--~~~---~--~~
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
-~--~~~~--~~--~--~---
Re: AJAX Delete
First, I would move the Configure::write('debug', 0); to within the if
block. Then if you hit that URL in your browser you can see any
specific errors.
On Mar 3, 2:46 pm, Dave Maharaj :: WidePixels.com
d...@widepixels.com wrote:
I am trying to delete a record using an AJAX link. The entry gets deleted,
effects and all that great stuff but after clicking delete I get
ERROR: The requested address 'users/delete_quote/(id number trying to
delete)' was not found on this server.
Now I am deleting the quote from the user page.
Tables Users haveMany Quotes
my users controller code :
function delete_quote($id)
// delete selected quote from profile
{
Configure::write('debug', 0);
if ($this-RequestHandler-isAjax()) {
$this-Quote-del($id);
}
}
Ideas of whets wrong or missing?
Thanks
Dave
--~--~-~--~~~---~--~~
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
-~--~~~~--~~--~--~---
