Tuesday 1 December 2015

ColdFusion: I learn something about query of query

G'day:
Just a quick one (I'm supposed to be doing Clojure this morning, not CFML). Here's somethng I did not know about QoQ in CFML. Well: in ColdFusion's implementation of QoQ. It's LIKE statement supports (very limited) regex patterns in its value.

Here's an example:

colours = queryNew("id,en,mi", "integer,varchar,varchar", [
    [1,"red","whero"],
    [2,"orange","karaka"],
    [3,"yellow","kowhai"],
    [4,"green","kakariki"],
    [5,"blue","kikorangi"],
    [6,"indigo","poropango"],
    [10,"violet","papura"]
]);

coloursWithOorU = queryExecute(
    "SELECT * FROM colours WHERE mi LIKE :pattern",
    {pattern={value="%[ou]%"}},
    {dbtype="query"}
);

writeDump(var=coloursWithOorU, format="text", metainfo=false);

And the result:

query

 
[Record # 1] 
en: red 
id: 1 
mi: whero
 
[Record # 2] 
en: yellow 
id: 3 
mi: kowhai
 
[Record # 3] 
en: blue 
id: 5 
mi: kikorangi
 
[Record # 4] 
en: indigo 
id: 6 
mi: poropango
 
[Record # 5] 
en: violet 
id: 10 
mi: papura

Cool. Note this does not work on Lucee.

I dunno what the grammar of the patterns are, but it's not simply standard CFML regex patterning. For example Initially I tried to have a pattern which would match words of six letters or more (ie: .{6}), but that didn't work. I was gonna say "it'd be really grand if Adobe actually documented this stuff", but actually they have! It's right there on the "Query of Queries user guide" page. OK, so the grammar is very limited. Just to what I've shown, basically: single character classes. It doesn't even support repetition modifiers. So it's a bit disappointing that the grammar is so limited, but it's handy nevertheless.

Thanks to Tim Brenner on the CFML Slack Channel for bringing this to my attention!

--
Adam