> -----Original Message-----
> From: Ray Champagne [mailto:[EMAIL PROTECTED]
> Sent: Thursday, September 29, 2005 11:27 AM
> To: CF-Talk
> Subject: ways to cache a query other than 'cachedwithin'
>
> I've got a simple query that will grab all my navigational menu items.
> pretty simple. Now, I've added Mike D's cf_maketree script to modify
> the query by adding another field, called maketreesortlevel, that stores
> the tree level so that I can output the menu items in the correct order
> and depth. this query gets rather big, since I've got over 1300
> categories and subcategories, so I'd like to be able to cache it. i
> know how to use cachedwithin as a cfquery attribute, but this won't work
> here, since I am modifying that query. is there another way to cache a
> query? here's the code, if it helps:
I think there might be some confusion here... you're "cfif" statement isn't
doing what I think you think its doing. ;^)
When you cache a query using cachedwithin it caches the query results keyed
to the SQL statement (and information about it like user and password) - it
doesn't cache the VARIABLE you're using however. Essentially if you run
that EXACT SQL again you'll get the cached results.
In your case this line:
> <cfif NOT isdefined("qry_get_topcats") or isdefined("attributes.refresh")>
Doesn't do what you think it does. The variable "qry_get_topcats" is a
local ("variables" scoped) variable and so is created at the beginning of a
request and destroyed at the end - this line will never prevent the code
from being run (unless it's call twice in the same request... if it were in
an include for example).
The variable "qry_get_topcats" is not cached. The SQL Statement you made
("SELECT Catefory_ID...") is linked to the cached data.
As it is I think your CFModule call will be run every-single request.
Now... as to the actual problem. I'm going to throw out an idea, but I
don't know if it'll work. It should be easy to try tho'.
The query itself is cached by CF (and linked to the SQL Statement). But it
can still be modified (as you see when you run your CFModule call).
So, instead of checking for the query, check for your changes. The first
time the query is run it's cached. But the "maketreesortlevel" column
doesn't exist, your CFModule adds that.
If the query is pulled from cache it will not change. However once the
query is rerun it will be "fresh" again (no "maketreesortlevel" column). So
something like this might work (I've abbreviated some of the code):
<cfquery name="qry_get_topcats" ... cachedwithin="#Request.Cache#">
SELECT Category_ID, Name, Parent_ID, ParentIDs
FROM Categories
WHERE Categories.Display = 1
ORDER BY Priority, Name
</cfquery>
<cfif NOT IsDefined("qry_get_topcats.maketreesortlevel")>
<cfmodule
template="make_tree.cfm"
Query="#qry_get_topcats#"
Result="qry_get_topcats"
Unique="category_ID"
Parent="Parent_ID">
</cfif>
This caches the query. Running the same SQL Statement again will NOT result
in a database hit until the cache timeout occurs. However the next block
checks for your modifications - if it doesn't find them it adds them.
Now - finally, to reset the query when you want... I believe (but I may be
wrong) that calling the query with a "cachedwithin" of zero should
effectively reset it. I think this might also result in two calls to the
query however (one to reset it, another to recache it).
Somebody else probably has a cleaner solution... but it would be something
like this:
<cfif IsDefined("attributes.refresh")>
<cfquery name="qry_get_topcats" ... cachedwithin="0">
SELECT Category_ID, Name, Parent_ID, ParentIDs
FROM Categories
WHERE Categories.Display = 1
ORDER BY Priority, Name
</cfquery>
<cfelse>
<cfquery name="qry_get_topcats" ... cachedwithin="#Request.Cache#">
SELECT Category_ID, Name, Parent_ID, ParentIDs
FROM Categories
WHERE Categories.Display = 1
ORDER BY Priority, Name
</cfquery>
</cfif>
<cfif NOT IsDefined("qry_get_topcats.maketreesortlevel")>
<cfmodule
template="make_tree.cfm"
Query="#qry_get_topcats#"
Result="qry_get_topcats"
Unique="category_ID"
Parent="Parent_ID">
</cfif>
I hope this helps,
Jim Davis
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Find out how CFTicket can increase your company's customer support
efficiency by 100%
http://www.houseoffusion.com/banners/view.cfm?bannerid=49
Message: http://www.houseoffusion.com/lists.cfm/link=i:4:219633
Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4
Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4
Unsubscribe:
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
