Caching Queries and Clearing the Cache with the "tags" Attribute

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:

  1. The first argument of cacheClear() is called filterOrTags and 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 a filter. If you pass an array, it will treat the first argument as tags.

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>
  1. 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>
  1. 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>
9 Likes

@dmurphy the only issue with the tagging code is that it had to be implemented in a generic way to support caching systems that do not support indexing. So clearing tags will loop through all records to delete them. In the system that this was based on, I use MongoDB as my cache and the tags are indexed so that clearing is extremely fast.