Marc,
Here's how I addressed that issue. This may or may not be considered good
practice, and there may be better ways to do it.
1. I created a view in the database which joins all the tables together. I
realize I'm losing a bit of portability here, but that's not an issue on
this project, and most DBMS's support views anyway.
2. I created a reactor object which points to my view. I can then create a
record object which contains all of the fields in my view, and I can refer
to them directly through the record. I'm basically flattening the table
schema.
3. The issue is that I cannot perform any creates or updates on that record
object as it points to a multi-table view, so I still need my "real" reactor
object (which is similar to the one you describe).
Here's an example of my configuration for a table object and a view object:
<object name="tblCampaign" alias="Campaign">
<hasOne name="Client">
<relate from="ClientId" to="ClientId" />
</hasOne>
<hasOne name="User" alias="UpdateUser">
<relate from="LastUpdateUserId" to="UserId"
/>
</hasOne>
<hasMany name="Application">
<relate from="CampaignId" to="CampaignId" />
</hasMany>
</object>
<object name="vwCampaign" alias="CampaignView">
</object>
So, when I only need to read data, I create an instance of the CampaignView
object, and when I need to alter data, I create an instance of the Campaign
object.
4. I my service layer, the method that returns the object accepts an
argument for "ReadOnly". If that argument is true, then I return an
instance of the CampaignView record, otherwise I return an instance of the
Campaign record.
5. In my controller (I'm using Model-Glue), I pass an argument along with my
message that requests the object. For any given event I know whether I need
a ReadOnly object or not, so this works well for me. For example:
<message name="NeedCampaign">
<argument name="ReadOnly"
value="true" />
</message>
I realize I have added a layer of complexity to everything, but I too did
not like the fact that I had to run several queries to return data that the
DBMS could easily return in one query via a view.
Finally, you could certainly achieve the above with OO queries, but I didn't
see the point when the DBMS offers the functionality of the view.
Regarding query caching, I'm also doing some of that. I've built it by hand
into my service layer for the objects that I wish to cache.
Hope that helps,
Bob
-----Original Message-----
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On
Behalf Of Marc
Sent: April 30, 2007 1:12 PM
To: [email protected]
Subject: [Reactor for CF] Reducing number of queries
Wanted to make sure I'm not missing something here:
Right now I have this configuration:
------------------------------------
<object name="Newsletter" alias="newsletter">
<hasMany name="Article">
<relate from="newsletterId" to="newsletterId" />
</hasMany>
</object>
<object name="Article" alias="article">
<hasOne name="Image">
<relate from="imageId" to="imageId" />
</hasOne>
<hasOne name="Newsletter">
<relate from="newsletterId" to="newsletterId" />
</hasOne>
<hasOne name="Category">
<relate from="categoryId" to="categoryId" />
</hasOne>
</object>
<object name="Image" alias="image" />
------------------------------------
And I'm running this code:
------------------------------------
<cfset curArticle =
application.serviceFactory.getBean('articleService').getLeadArticle() />
<cfset request.title = curArticle.getTitle() /> <cfset request.pageSubtitle
= curArticle.getCategory().getCategoryName() /> <cfset request.pageImageId =
curArticle.getImageId() /> <cfset request.pageImageType =
curArticle.getImage().getImageType() /> <cfset request.pageDate =
curArticle.getNewsletter().getNewsletterDate() /> <cfset
request.pageImageAlt = curArticle.getImage().getFriendlyName() />
------------------------------------
Which is resulting in a total of FOUR queries being run:
------------------------------------
(1)
SELECT [article].[articleId] AS [articleId], [article].[newsletterId] AS
[newsletterId], [article].[categoryId] AS [categoryId], [article].[title]
AS [title], [article].[body] AS [body], [article].[imageId] AS [imageId],
[article].[isLead] AS [isLead], [article].[crd] AS [crd] FROM [Article] AS
[article] WHERE [article].[articleId] = ?
(2)
SELECT [Category].[categoryId] AS [categoryId], [Category].[categoryName]
AS [categoryName], [Category].[crd] AS [crd] FROM [Category] AS [Category]
WHERE [Category].[categoryId] = ?
(3)
SELECT [image].[imageId] AS [imageId], [image].[imageType] AS [imageType],
[image].[friendlyName] AS [friendlyName], [image].[crd] AS [crd] FROM
[Image] AS [image] WHERE [image].[imageId] = ?
(4)
SELECT [newsletter].[newsletterId] AS [newsletterId],
[newsletter].[newsletterDate] AS [newsletterDate], [newsletter].[crd] AS
[crd] FROM [Newsletter] AS [newsletter] WHERE [newsletter].[newsletterId] =
?
------------------------------------
And the only way to make this more efficient (reducing it to a single query)
would be to write a custom query in one of the user-editable CFCs in my data
directory?
Another way that I'd considered making this more efficient was to somehow
cache the above queries, but I don't know how to do that with reactor...
I would like to reduce the number of database calls here, as I believe this
particular function is going to receive a lot of traffic. I ran into
trouble in the past with my use of reactor not holding up under traffic, and
clearly I don't really need four database calls here, if I had a query that
returned all the data in one object somehow...
Can anyone point me in the right direction or offer advice on this?
Thanks,
M
-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --
-- -- Reactor for ColdFusion Mailing List [EMAIL PROTECTED] Archives
at: http://www.mail-archive.com/reactor%40doughughes.net/
-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --
-- --
-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --
Reactor for ColdFusion Mailing List
[EMAIL PROTECTED]
Archives at: http://www.mail-archive.com/reactor%40doughughes.net/
-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --
