Alternatives to Query of Query

October 1, 2019
I try to bend the internet to my will.
Wizard 31 posts
Followers: 19 people
6

Alternatives to Query of Query

I try to bend the internet to my will.
Wizard 31 posts
Followers: 19 people
October 1, 2019

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
Comments (6)
2019-10-09 07:56:39
2019-10-09 07:56:39

Thanks for the clarifications Charlie 

Like
2019-10-09 07:39:52
2019-10-09 07:39:52

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)
>
aliaspooryorik
's comment
2019-10-09 12:31:35
2019-10-09 12:31:35
>
aliaspooryorik
's comment

Thanks for the reply, that makes sense.  I went and voted and maybe the discussion here will help get it on the radar in addition to the bug tracker.

Like
>
aliaspooryorik
's comment
2019-10-09 15:04:30
2019-10-09 15:04:30
>
aliaspooryorik
's comment

As for the objects created in a function being released for GC after the function finishes, we should clarify for readers that this would be true as long as you var scope (or use the local scope) for the variables.

You did for your var q, but then that was in a function creating the query that would be duplicated. If the rest of the code (creating the dupe) were itself in some other function or method of a CFC, that is where it would be important to have var-scoped the rest. (I know you and Grae know this. I’m writing for other readers.)

Like
(1)
2019-10-08 21:22:07
2019-10-08 21:22:07

Nice stuff, John (though the need to duplicate things in those couple of cases may negate the value of this over q of q).

That said, you have not really said why this approach would be “better” than a q of q. That may help some readers. It may be merely that it’s a more modern (functional programming) approach, sure.

To that point, though, I’ll add that while you refer to things like querysort, queryfilter, and querymap, then you never use them. Instead, you use the implicit “higher order” functions, appended to the objects. For readers who may want to understand more on that, see some other resources on the topic like https://coldfusion.adobe.com/2017/10/map-reduce-and-filter-functions-in-coldfusion/ and http://ryanguill.com/functional/higher-order-functions/2016/05/18/higher-order-functions.html.

Finally, you conclude with a mention of the option to use “arrow functions” also, but we should note for readers that that is only supported in CF2018 (and that as of only its update 5 from last month) but it has not yet been offered in CF2016 (even per its update 12 of last month). Lest anyone may ask: I am not aware of whether the plan is for those to be offered in CF2016 in the future.

Hope that’s helpful. None of it is meant as criticism. Again, thanks for the post. Just expanding on things a bit for some readers.

Like
(4)
2019-10-08 20:46:00
2019-10-08 20:46:00

Thanks for this, i can see some uses right away.  Couple questions.

Does this run faster than query of queries?

Some cases required duplicating the query to do the function.  Any concerns on memory usage or is the idea you won’t need to do that enough that it matters?

 

Thanks

Like
Add your comment