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