TQL Transfer lexicon for fusebox
Transfer is getting better all the time, with some recent speed improvements it keeps growing. If you've not used Transfer in fusebox yet check out this post.
Since March Transfer has supported TQL, which is transfers own scripting language:
There is also a scripting language that allows you to perform database queries based on the information and naming scheme that you set up in your transfer configuration file called Transfer Query Language (TQL). TQL is very similar to SQL, however since Transfer already knows about the relationships in your system, you don't have to write as much code to perform complicated queries against your database.
This is a fantastic addition as it allows you to quickly manipulate your data. One thing that I thought could make easy to use via fusebox was to create a lexicon, which I have done here:
// author: Nick Tong - http://succor.co.uk | http://talkwebsolutions.co.uk // usage: // <transfer:tql
// tql=" from post.Post as Post join system.Category join user.User order by Post.dateTime desc "
// queryName="myQueryName">
// <transfer:parameter name="id" value="#attributes.id#" />
// </transfer:tql> if (fb_.verbInfo.executionMode is "start") {
// validate attributes // object - string if (not structKeyExists(fb_.verbInfo.attributes,"tql")) {
fb_throw("fusebox.badGrammar.requiredAttributeMissing",
"Required attribute is missing",
"The attribute 'tql' is required, for a 'tql' verb in fuseaction #fb_.verbInfo.circuit#.#fb_.verbInfo.fuseaction#.");
}
// queryName - string if (not structKeyExists(fb_.verbInfo.attributes,"queryName")) {
fb_throw("fusebox.badGrammar.requiredAttributeMissing",
"Required attribute is missing",
"The attribute 'queryName' is required, for a 'tql' verb in fuseaction #fb_.verbInfo.circuit#.#fb_.verbInfo.fuseaction#.");
}
// generate code: fb_appendLine('<cfset #fb_.verbInfo.attributes.queryName# = ' &
'myFusebox.getApplication().getApplicationData().transferFactory.getTransfer().createQuery("#fb_.verbInfo.attributes.tql#") />');
// prepare for any parameters: fb_.verbInfo.parameters = structNew();
} else {
for (fb_.p in fb_.verbInfo.parameters) {
fb_appendLine('<cfset #fb_.verbInfo.attributes.queryName#.setParam("#fb_.p#",#fb_.verbInfo.parameters[fb_.p]#) />');
}
fb_appendLine('<cfset #fb_.verbInfo.attributes.queryName# = ' &
'myFusebox.getApplication().getApplicationData().transferFactory.getTransfer().listByQuery(#fb_.verbInfo.attributes.queryName#) />');
fb_appendLine('<cfset myFusebox.trace("Transfer","Created TQL Query") />');
}
</cfscript>
If you save this in your lexicons folder as TQL.cfm you can then use it like this in your circuit.xml.cfm file:
<tr:parameter name="startDate" value="lsDateFormat(now())" />
</tr:tql>
<cfset myQueryName.setParam("startDate",lsDateFormat(now())) />
<cfset myQueryName = myFusebox.getApplication().getApplicationData().transferFactory.getTransfer().listByQuery(myQueryName) />
<cfset myFusebox.trace("Transfer","Listed TQL Records") />
So what's this TQL stuff? In the example above you can see that i pass though
:startDate is the name of the paramater that we are passing in.
Simple stuff huh - this can get a lot more intelligent as show in Marks post:
post.Post as Post
join system.Category
join user.User
order by
Post.dateTime desc
As mentioned above we use the Transfer parameter lexicon, because of this you will need to edit your parameter.cfm file so it can be called via the TQL lexicon. Update line 12 to:
If you have any comments please let me know. TQL is very powerful and a great addition to the Transfer framework.
i.e.
tql=" from event.event as event where event.startDate < :todaysDate "
And thanks for the useful tag.
Is the circuit.xml.cfm file generally the right place to be generating TQL? Isn't this the equivalent of having inline SQL within your controller - surely not where it belongs within the MVC paradigm?
@Dan - The circuit in question is the 'model' circuit. So instead of calling the object to do you crud (for example) you call Transfer.