Friday 17 July 2015

CFML suggestion: make queries implement an array interface

This was not what I was gonna write about today, but it popped into my head as I was walking up upstairs to the office, so in a carpe diem moment, I'll do this one instead.

Queries are one of the most fundamental data structures in CFML; in fact perhaps the most fundamental data structure for CFML's original USP which was to pull data from a DB and get it into a web page. But if one thinks about it... it's one of the least functional. Beyond looping over 'em, there are a total of eight functions relating to them (from Query Functions). Four of those are all about building queries (queryNew(), queryAddColumn(), queryAddRow(), querySetCell()); two of them are for converting to another data type (valueList() and quotedValueList()); one to check of something is a query in the first place (isQuery()); and then there's one function for actually doing something with said query object: queryConvertForGrid(). And one basically should not be using that as it's only useful for using with <cfgrid>, and one should not be using that. Basically if one wants to manipulate a query object, on needs to fall back to QoQ, which is a bit of a rubbish way of going about things.

Contrast this with list functions: there's 28 of them. And lists are crap and should be avoided!

Queries are basically a type of numerically-indexed ordered collections, which makes them pretty array-ish. And if one looks at the array functions (29 of those, btw), then there's an awful lot there which make sense to work on queries too. I'm going to look at these specifically from the point of view of object methods not procedural functions; it's easier to see the uniform API one can get when considering things from that perspective.

  • Query.append(): adds a row to the end of a query, or concatenates two queries together.
  • Query.average(): nah. One would use .reduce() for this.
  • Query.clear(): does the query equivalent of TRUNCATE TABLE in SQL.
  • Query.deleteAt(): removes row(s?) from the query.
  • Query.insertAt(): sticks 'em back in again. These two operations are possible with QoQ, but not pretty.
  • Query.contains(): no. .some().
  • Query.each(): essential. I can't believe ColdFusion does not have this yet (Lucee does).
  • Query.slice(): copies rows from within a query.
  • Query.isDefined(): no. It's not valid for queries.
  • Query.isEmpty(): not really valid for queries either: one can just check the recordCount.
  • Query.len(): I'd put this in anyhow, even if one can use recordCount: it's such a ubiquitous function for collections.
  • QueryMax(): no, use .reduce()
  • Query.min(): no, use .reduce()
  • Query.delete(): no, use .filter()
  • Query.filter(): removes rows from the query based on the filter rules.
  • should already have been implemented.
  • already got it. Well, not as a method. It would be good to have an OO way of creating a query though, without falling back to queryNew().
  • Query.prepend(): adds rows (/another query) to the front of a query.
  • Query.reduce(): should already have been implemented.
  • Query.resize(): not appropriate for queries.
  • Query.set(): possibly use .filter() and .insertAt() instead?
  • Query.sort(): sorts the query.
  • Query.find(): possibly. Although would be better placed to use .filter() or .reduce(), perhaps?
  • Query.findAll(): use .filter().
  • Query.sum(): use .reduce().
  • Query.swap(): swaps specified rows.
  • Query.toList(): use .reduce().
  • doesn't make sense.
  • String.listToQuery(): use .reduce().
  • Query.findNoCase(): use .filter() or .reduce().
  • Query.findAllNoCase(): use .filter() or .reduce().
Some of the red ones would be predicated on other methods like .reduce(), .filter() and .some() being implemented. Speaking of new methods (not mentioned already), we could also have these:

  • Query.every(Function): returns true if all rows in the query fulfills the test. ALl collections should have this method.
  • Query.filter(String): where String is an SQL WHERE clause, and query and clause are passed to the QoQ engine. Maybe call this Query.where() instead? Is there further scope for, Query.join(), Query.groupBy(), Query.orderBy() too?
  • Query.reduceRight(): all collections should have this method. Same as .reduce(), but starts from the last element, not the first.
  • Query.some(Function): returns true if a row in the query fulfills the test defined by the callback. All collections should have this method.
Also, with queries implementing an array interface, a query should then be able to be used anywhere an array is required.

This would promote query objects beyond just something to loop over, and make them more like the first class citizens CFML ought to treat them as.

Thoughts? Ticket raised: 4022420.