cachedWithin not working for queries with at least 1 queryParam

dev
1

Server: Lucee 6.0.0.519-RC

Decription of the problem:
Caching queries and purging them through their tag name is working for queries without any queryparams. From the documentation I understand that it should also work for a queryparam provided the value of that param is the same.

Working example:

public query function quicklist() {

	var qService = new query();
	var result;

	try {
		qService.setDatasource(Application["dataSource"]);
		qService.setName("QuickUsers");
		qService.setSql("select hex(u.id) as id, u.username as username, u.wallet as wallet FROM users u ORDER BY u.username");
		result = qService.execute(cachedWithin:createTimespan(0,0,60,0), tags:"QuickUsers").getResult();
	} catch (any e) {
		// do stuff (logging / cleanup)
		Application.logging.log(e);
	}        
    return result;
}

Subsequent calls tap into the cached query for the statement above.

Different scenario below:

public query function getAttendees( required string editionid) {

	var qService = new query();
	var result;

	try {
		qService.setDatasource(Application["dataSource"]);
		qService.setName("AllAttendees_#arguments.editionid#");
		qService.addParam(name="editionid", value="#uuidToBinary(arguments.editionid)#", cfsqltype="cf_sql_binary");    
		qService.setSql("select hex(a.id) as attendanceid, hex(u.id) as userid, u.username as username, u.email as email, 
						 c.tag as tag, a.paid as paid, a.creationdate as subscriptiondate, a.lastupdate as lastupdate 
						from attendance a 
						LEFT JOIN users u ON a.userid = u.id 
						LEFT JOIN clans c ON a.clanid = c.id 
						where a.editionid = :editionid ORDER BY u.username;");
		result = qService.execute(cachedWithin:createTimespan(0,0,60,0), tags:"AllAttendees_#arguments.editionid#").getResult();
	} catch (any e) {
		// do stuff (logging / cleanup)
		Application.logging.log(e);
	}

    return result;
}

Even while specifying the same editionid, a new query is added to the cache everytime (visible in administrator’s queryCache number of elements cached.

2

No answer, but a couple of questions to help folks help you:

  • what happens if you remove the addparam and just use the var in the where? Not secure or optimal, of course. Just asking as a diagnostic.
  • and are you observing this as a change in behavior from 5 to 6, or just using it in 6 for the first time?

I’ve not used the tag feature for cached queries (which lucee adds and cf does not offer–though it offers a cacheid to help in such use cases).

3

If I comment out the queryParam and I use the argument inside the sql statement the caching behaves as expected.

		qService.setDatasource(Application["dataSource"]);
		qService.setName("AllAttendees_#arguments.editionid#");
		// qService.addParam(name="editionid", value="#uuidToBinary(arguments.editionid)#", cfsqltype="cf_sql_binary");    
		qService.setSql("select hex(a.id) as attendanceid, hex(u.id) as userid, u.username as username, u.email as email, 
						 c.tag as tag, a.paid as paid, a.creationdate as subscriptiondate, a.lastupdate as lastupdate 
						from attendance a 
						LEFT JOIN users u ON a.userid = u.id 
						LEFT JOIN clans c ON a.clanid = c.id 
						where hex(a.editionid) = '#arguments.editionid#' ORDER BY u.username;");
		result = qService.execute(cachedWithin:createTimespan(0,0,60,0), tags:"AllAttendees_#arguments.editionid#").getResult();
4

Digging a little deeper, inspired by Charlie’s remark:

I can also make it work with a queryParam, if it’s not of sqltype cf_sql_binary it seems:

		qService.setDatasource(Application["dataSource"]);
		qService.setName("AllAttendees_#arguments.editionid#");
		// qService.addParam(name="editionid", value="#uuidToBinary(arguments.editionid)#", cfsqltype="cf_sql_binary");
		qService.addParam(name="editionid", value=arguments.editionid);
		qService.setSql("select hex(a.id) as attendanceid, hex(u.id) as userid, u.username as username, u.email as email, 
						 c.tag as tag, a.paid as paid, a.creationdate as subscriptiondate, a.lastupdate as lastupdate 
						from attendance a 
						LEFT JOIN users u ON a.userid = u.id 
						LEFT JOIN clans c ON a.clanid = c.id 
						where hex(a.editionid) = :editionid ORDER BY u.username;");
		result = qService.execute(cachedWithin:createTimespan(0,0,60,0), tags:"AllAttendees_#arguments.editionid#").getResult();

Maybe the behaviour can be explained if the calculation on whether to use the cache is based on hashing the quaryparams and compare if hashes exist? Maybe the binary datatype breaks the hashing?

1 Like