October 1, 2019
Alternatives to Query of Query
Comments
(7)
October 1, 2019
Alternatives to Query of Query
I try to bend the internet to my will.
Newbie 34 posts
Followers: 24 people
(7)

CFML has had Query of Queries for years and it’s been a useful tool but in modern CFML there is no need to use it at all.

Before I go any further I should point out that if you are working with query objects then it’s likely that that best place to do sorting, filtering etc is directly in the database. Database engines are designed and optimised for these kinds of operations.

With that disclaimer out the way, let’s start off with some interesting data (normally this would come from a database – but for demonstration I’m constructing the query object using QueryNew). I’ve put it in function so we can call it easily in each example.

function seed() {
  var q = QueryNew(
    "rank,code,team,points,previous_points,rank_change",
    "integer,varchar,varchar,integer,integer,integer",
    [
      [1,"USA","USA",2101,2123,0],
      [2,"GER","Germany",2072,2057,0],
      [3,"ENG","England",2049,2021,1],
      [4,"FRA","France",2043,2046,-1],
      [5,"CAN","Canada",2006,2006,0],
      [6,"AUS","Australia",2003,1999,0],
      [7,"JPN","Japan",1991,1984,1],
      [8,"NED","Netherlands",1967,1987,-1],
      [9,"SWE","Sweden",1962,1976,0],
      [10,"BRA","Brazil",1944,1964,0]
    ]);
    return q;
}

Query of queries is often used to sort data, we can do that using QuerySort (or the member function queryObject.sort):

q = seed();
sorted = q.sort(function(prev, next) {
  return compare(prev.team, next.team);
});
writeDump(var=sorted, label="sorted");
writeDump(var=q, label="original");

Running the above code will output the query sorted by the team name A-Z.

This is great – much nicer than a query of query and we can use it in cfscript.

Well, I’m afraid it’s not all good news as the sharp eyed will have spotted. Unfortunately in ColdFusion the above code will also mutate the original query. I don’t think it should, but it does (running on ColdFusion 2018 update 5 at the time of writing). This means that the original query object referenced in q is also sorted A-Z. We really don’t want the original query object to change. We can mitigate against this by duplicating the query object before sorting it.

q = seed();
sorted = q.duplicate().sort(function(prev, next) {
  return compare(prev.team, next.team);
});
writeDump(var=sorted, label="sorted"); // <- sorted A-Z
writeDump(var=q, label="original"); // <- maintains original sort order

Now running the code gives us two query objects that are distinct references so the original query is as it was.

Of course, sorting can be based on any number of things and we can do that easily with QuerySort.

q = seed();
sorted = q.duplicate().sort(function(prev, next) {
  if (a.rank_change != b.rank_change) {
    return b.rank_change - a.rank_change;
  }
  return (a.points-a.previous_points) - (b.points-b.previous_points);
});
writeDump(var=sorted, label="sorted");

Another thing that Query of Queries gets used for is for filtering a recordset.
For that task we can use QueryFilter.

q = seed();
filtered = q.filter(function(el) {
  return el.rank_change != 0;
});

writeDump(var=filtered, label="filtered"); // <- 4 rows
writeDump(var=q.len(), label="q.len"); // <- uh-oh! 4 rows

Running this outputs

Once again ColdFusion has the issue where the original query is also mutated, so you must use duplicate else you could end up with some nasty side-effects. This is not obvious when you read the code so you need to be aware of this unexpected behaviour in ColdFusion.

q = seed();
filtered = q.duplicate().filter(function(el) {
  return el.rank_change != 0;
});

writeDump(var=filtered, label="filtered"); // <- 4 rows
writeDump(var=q.len(), label="q.len"); // <- got 10 rows as expected

You may also use Query or Queries to change values in rows. This is where we can use QueryMap.

q = seed();
mapped = q.map(function(el) {
  el.team &= " [#el.code#]";
  return el;
});

writeDump(q); // <- YAY! not mutated
writeDump(mapped);

Thankfully QueryMap doesn’t mutate the original query so no need for the duplicate workaround here – YAY!

QueryMap also allows us to add fields to the recordset.

q = seed();
newQuery = QueryNew("rank,code,team,points,previous_points,rank_change,points_change");
mapped = querymap(q, function(el) {
  el['points_change'] = el.points-el.previous_points;
  return el;
}, newQuery);

writeDump(q); // <- unchanged
writeDump(mapped); // <- has 'points_change' field

Note that we need to construct a new query object, with the new field, which is then passed as the 3rd argument to QueryMap. I think the above example is a bit clunky, so I think I’d prefer to convert to an array of structs with the new field in each struct. To do that we can use QueryReduce.

q = seed();
mapped = queryreduce(q, function(acc, el) {
  el['points_change'] = el.points-el.previous_points;
  acc.append(el);
  return acc;
}, []);

writeDump(q); // <- all good
writeDump(mapped); // <- array

This time we end up with the original query intact and an array of structs with a calculated ‘points_change’ field.

So there you are. Having read this far I hope that it was interesting!

If you really want to get fancy you can write your code with arrow functions. For example the above code can be written as:

q = seed();
mapped = q.reduce((acc, el) => {
  el['points_change'] = el.points-el.previous_points;
  acc.append(el);
  return acc;
}, []);

writeDump(q); // <- all good
writeDump(mapped); // <- array
7 Comments
Oct 22, 2019
Oct 22, 2019

One key reason not to use Query of Queries is that, if you have to maintain your codebase on both Lucee and ACF, QoQ is very slow on Lucee and specifically recommended against using.

Like
()
Oct 9, 2019
Oct 9, 2019

Thanks for the clarifications Charlie 

Like
()
Oct 9, 2019
Oct 9, 2019

The mutating of the original query is a bug (Lucee does not mutate it). The issue is raised here https://tracker.adobe.com/#/view/CF-4203366 please vote!

Is it faster? I really don’t know. I tend to code for readability rather than performance and then optimize later if required – I’ve not noticed any issues with it. If you are concerned about performance then I would expect a database engine to be much faster than using CF to sort / filter etc as that’s what they are built to do.

In terms of memory usage, having to duplicate it is annoying. If you don’t care about the original query being mutated then you don’t have to duplicate – just make sure you have good code coverage so you don’t trip up your future self when you forget about this annoying behaviour (did I mention people should vote for CF-4203366!).

Charlie knows way more about the inner mysteries of ColdFusion performance than I do, but I believe that if you do the manipulation inside a function, when the function exits, the references to variables created inside that function (apart from anything returned) are available for Garbage Collection, so I don’t think you’ll get a memory leak. That said, if you have large datasets, then the database is the best place to be manipulating them if at all possible.

Like
(1)
(2)
Add Comment