Tucked away in the release notes of Lucee 5.1.4 and 5.2 is a hidden gem related to caching strategies regarding queries. The <cfquery> tag, the function queryExecute(), and the component org.lucee.cfml.Query all allow the use of tagging a query as it is cached when using the attribute cachedWithin.
Note: The examples below assume you have set up a default query cache named myQueryCache in the Lucee admin. You can also set up the cache via the Lucee admin and copy the exact config to put it into your Application.cfc instead.
Let’s say you have a query that returns all teams from the database. You want to cache the results for a week, but you want to break the cache if there are any changes to teams. The following examples show how to add the query to the cache and then how to clear the cache after adding, deleting, or changing a team somewhere else in the application.
Example: cfscript version with queryExecute()
<cfscript>
// Add the query to the cache
queryExecute(
"SELECT * FROM teams",
{ },
{
"cachedWithin" : createTimespan( 7, 0, 0, 0 ),
"tags" : "allTeams"
}
);
// Clear the query from the cache after modifying teams
cacheClear( ["allTeams"], "myQueryCache");
</cfscript>
Example: Tag version with cfquery
<!--- Add the query to the cache --->
<cfquery name="bleh" cachedWithin="#createTimespan( 7, 0, 0, 0 )#" tags="allTeams">
SELECT * FROM teams
</cfquery>
<!--- Clear the query from the cache after modifying teams--->
<cfset cacheClear( ["allTeams"], "QueryCache" )>
Some key things to note:
- The first argument of
cacheClear()is calledfilterOrTagsand can be a string or an array (see cfdocs or the Lucee docs). If you pass a string, it will treat the first argument as afilter. If you pass an array, it will treat the first argument astags.
Example: cacheClear() filter vs tags
<cfscript>
// This does not use tags and instead filters for queries with the string of teams. This is not what we want if we're trying to use tags.
cacheClear( "teams", "queryCache")
// This clears any query from the cache that is using the name of "allTeams" for tags
cacheClear( ["allTeams"], "queryCache")
// This clears any query from the cache that is using name of "allTeams", "allEmployees", or "allLocations" for tags
cacheClear( ["allTeams","allEmployees","allLocations"], "queryCache")
</cfscript>
- The tags can be dynamic.
Example: Dynamic names for tags
<cfscript>
// Add the query to the cache
queryExecute(
"
SELECT *
FROM teams
WHERE team_id = :id
",
{
"id" : {
"value" : arguments.id,
"cfsqltype" : "cf_sql_integer"
}
},
{
"cachedWithin" : createTimespan( 7, 0, 0, 0 ),
"tags" : "allTeams_#arguments.id#"
}
);
// Clear the query from the cache after modifying teams
cacheClear( ["allTeams_#id#"], "myQueryCache");
</cfscript>
- Queries in the cache can share tags.
Example: Tags used for multiple queries all cleared at once
<cfscript>
// Add the query to the cache
//This query can be used for any number of IDs, but all use the same name for tags
queryExecute(
"
SELECT *
FROM teams
WHERE team_id = :id
",
{
"id" : {
"value" : arguments.id,
"cfsqltype" : "cf_sql_integer"
}
},
{
"cachedWithin" : createTimespan( 7, 0, 0, 0 ),
"tags" : "allTeams"
}
);
// This query also uses the name "allTeams" for tags
queryExecute(
"SELECT * FROM teams",
{ },
{
"cachedWithin" : createTimespan( 7, 0, 0, 0 ),
"tags" : "allTeams"
}
);
// Clear all queries using the name of "allTeams" for tags
cacheClear( ["allTeams"], "myQueryCache");
</cfscript>