Query of Queries really helped me out this morning. The Model-Glue team is working on a ColdFusion Builder extension and we need to get a list of files to inspect. There could be many types of files in a project that aren’t relevant to our purposes and I wanted to filter only the ones I need.
My Directory query contained lots of SVN specific files, which make for a good example of what we want to filter, before doing our inspection work.
Original Directory Query
What we need in this case is to filter anything that isn’t a CFM, CFC or XML file. The CFDirectory tag will allow only a single filter, so what do we do?
In ColdFusion, I would use listlast( filename, “.”) to look at the file extension but that would mean I’d have to unpack the query and either make a new one, or pack it in another datastructure. Waste of code, I tell ya. I ended up using Query of Queries and a LIKE statement to filter.
New Directory Query
The Code I Used for the Query of Queries
SELECT *
FROM arguments.sourceDirectoryQuery
WHERE lower(NAME) LIKE '%.cfm'
OR lower(NAME) LIKE '%.cfc'
OR lower(NAME) LIKE '%.xml'
The secret sauce is the lower() function on NAME, and the LIKE conditional with a wildcard % operator. I found this through the Query of Queries documentation and I also learned about a few other wildcard operators as well.
List of Wildcard Operators Supported In Query of Query LIKE conditional
- The underscore (_) represents any single character.
- The percent sign (%) represents zero or more characters.
- Square brackets ([ ]) represents any character in the range.
- Square brackets with a caret [^] represent any character not in the range.
- All other characters represent themselves.
Simple and maintainable. Just how I like it. Thanks #ColdFusion!