== Transfer Query Language ==

Transfer Query Language is another abstraction layer between the database and the Object model that the developer has created, that allows you to perform SQL like queries against the database.

This has been developed so that you can leverage the knowledge you already have about your Object Oriented model to do your gateway queries, as well as being able to do queries with less code, as Transfer already knows what columns are required per table, and the relationships between objects.

Transfer then takes the responsibility to translate the TQL script, combined with your Object configuration file into SQL that is then run against the database.

=== The Transfer Query Object ===

The Transfer Query object is a CFC of type [http://docs.transfer-orm.com/html/transferapi/transfer/com/tql/Query.html transfer.com.tql.Query] , and is used as the main holder for information about the TQL Script.

To do a [http://docs.transfer-orm.com/html/transferapi/transfer/com/Transfer.html#listByQuery() Transfer.listByQuery()] or a [http://docs.transfer-orm.com/html/transferapi/transfer/com/Transfer.html#readByQuery() Transfer.readByQuery()] you must first create a new TQL Query object. For more information on these methods, check the [http://www.transfer-orm.com/transfer/documentation/database.html Database Management Methods] section.

==== Creating a Query Object ====

To create a query object, call the [http://docs.transfer-orm.com/html/transferapi/transfer/com/Transfer.html#createQuery() createQuery(tql)] method on the [http://docs.transfer-orm.com/html/transferapi/transfer/com/Transfer.html transfer.com.Transfer] CFC with the required TQL Script as an argument. This will return the TQL Query object that is based on the entered TQL script.

For example:

<source lang="coldfusion">
query = getTransfer().createQuery("from post.Post");
</source>

Where 'query' is the resultant [http://docs.transfer-orm.com/html/transferapi/transfer/com/tql/Query.html transfer.com.tql.Query] object.

==== TQL Query Methods====

The TQL Query Object has a series of methods that can effect how the TQL script is resolved by Transfer:

===== Query.setAliasColumns(aliasColumns) =====
([http://docs.transfer-orm.com/html/transferapi/transfer/com/tql/Query.html#setAliasColumns() API])

By default, database columns in the resultant query are aliased to the name of the ''Property'' that they are assigned in the Object configuration XML file. To turn this off, so that the query uses the original column names:

<source lang="coldfusion">
query.setAliasColumns(false);
</source>

By default, aliasing columns is set to 'true'.

===== Query.setDistinctMode(distinctMode) =====
([http://docs.transfer-orm.com/html/transferapi/transfer/com/tql/Query.html#setDistinctMode() API])

By default, the resultant SQL query from the TQL script is not a <code>SELECT DISTINCT...</code> query. Since there is no <code>DISTINCT</code> keyword in TQL, the query must be set to 'DistinctMode' to produce the same effect. e.g.

<source lang="coldfusion">
query.setDistinctMode(true);
</source>

===== Query.setParam(name, [value], [type], [list], [isNull])] =====
([http://docs.transfer-orm.com/html/transferapi/transfer/com/tql/Query.html#setParam() API])

TQL has the ability to specify place holders for mapped paramters within the TQL by using a ':' in front of the mapped parameter's name. For example, the TQL:

<code><pre>
from post.Post as Post where Post.name = :postName	
</pre></code>

Has the mapped parameter 'postName' that has to be set before the query can be run.

To set the mapped parameter a value, the method Query.setParam is used. For example to set the 'postName' mapped param to 'foo':

<code><pre>
query.setParam("postName", "foo", "string"); 
</pre></code>

The type argument can be set to the same 'type' attributes that can be found on ''Property'' elements in the object configuration xml file, i.e. string, numeric, boolean, date, binary, GUID, and UUID. By default, it is set to 'string'.

The 'list' argument tells the Query object that the value being set to the mapped parameter is actually a list of values, exactly like the 'list' attribute on <code><cfqueryparam></code>. By default this set to false.

Again, exactly like <code><cfqueryparam></code>, the 'null' argument takes a boolean that sets the value of the mapped parameter to <code>NULL</code>. By default, this is set to false.

===== Query.setCacheEvaluation(cacheEvaluation) =====
([http://docs.transfer-orm.com/html/transferapi/transfer/com/tql/Query.html#setCacheEvaluation() API])

Transfer caches the SQL which is generated from your TQL query when
executing queries to increase performance. This cached evaluation is
not specific to the mapped parameter values that are used in the
query. This cache is enabled by default.

When dynamically generating TQL queries that will *never* be executed
again, or executing a large number of vastly different TQL queries you
may disable the evaluation cache to save memory.

In general you should not modify this setting.

To disable the cache call:

<source lang="coldfusion">
query.setCacheEvaluation(false);
</source>

By default evaluation caching is set to true.

<messagebox type="warning">Prior to version 1.1 of Transfer, the default setting for cache evaluation is 'false', not 'true'</messagebox>

=== TQL Custom Tags ===

If you don't wish to use the TQL Query Object, you can also use the [[TQL Custom Tags]] that are also available, and provides the same functionality with a different style of syntax.

=== Transfer Query Language Syntax ===

The Transfer Query Language is its own scripting language that was written in Java and ColdFusion. The following is the syntax that is allowed when using TQL.

Legend [ x ] - 'x' can appear 0 or 1 times <br /> [ x ]* - 'x' can appear 0 to infinity times. <br /> ( x ) - 'x' must appear <br /> x | y - 'x' or 'y' <br /> "(" - a real ( <br /> ... - and so on...

==== Identifiers ====

* ''class'' - A class name as defined in the Transer Object Configuration file, i.e. 'post.Post' or 'system.Category'
* ''property'' - The name of a property on the class that it is associated with. i.e. 'post.Post.Name' 'Name' would be the property 'Name' on 'post.Post'.
* ''columnAlias'' - The name you wish the column, i.e. the ''property'' to be in the resultant query. Can be of the pattern [ a-z | A-Z ][ a-z | A-Z | 0-9 | _ ]* for Example: 'post.Post.name as postName', where 'postName' is the columnAlias.
* ''classAlias'' - The shorthand version of the ''class'' you wish this to be in the query. Can be of the pattern [ a-z | A-Z ][ a-z | A-Z | 0-9 | _ ]*. For example: 'post.Post as Post', where 'Post' is the classAlias.
* ''mappedParameter'' - An identifier of pattern :[ a-z | A-Z | 0-9 | _ ]*, that is set in the TQL Query object before being evaluated. For example: 'from post.Post where post.Post.name = :name' where ':name' is a mappedParameter.
* ''subSelectStatement'' - A sub-select TQL statement. Must start with a 'select...'. For example: 'from post.Post as Post where post.Name IN (select User.name from user.User as User)' where 'select User.name from user.User as User' is a subSelectStatement.

==== Basic List Queries ====

<code>from ''class''</code>

This produces a query that selects all the columns that defined in the ''class''es object definitions that are present in the 'from' statement.

For Example: <code>from post.Post</code> will return all the columns as defined from the 'post.Post' object, from the table that 'post.Post' is configured to.

<code>select * from ''class''</code>

This code, produces exactly the same result as above. The '*' operator tells Transfer to resolve all properties of the given ''class''es in the 'from' statement.

For example: <code>from post.Post</code> is exactly the same as <code>select * from post.Post</code>

==== Selecting specific columns ====

<code>select ''class''.''property'' [, ''class''.''property'']* from ''class''</code>

To select specific properties from a query, you can specify them much like you would columns in a regular SQL expression.

For example: <code>select post.Post.postName, post.Post.postDate from post.Post</code> will select the 'postName' and the 'postDate' properties from the table that represents 'post.Post'.

==== Aliasing column names ====

<code> select ''class''.''property'' [as ''columnAlias''] from ''class'' </code>

It is also possible to manually alias columns to specific names.

For example: <code>select post.Post.name as title from post.Post</code> where 'title' is the alias, will alias the property 'postName' to 'title' in the query resultset.

==== Aliasing columns names, while also selecting all ====

<code> select ''class''.''property'' [as ''columnAlias''] [, ''class''.''property'' [as ''columnAlias''] | * ]* from ''class'' </code>

Often you will only want to alias some columns in a TQL query, but still automatically select the rest. By including the '* operator into the 'select' part of the TQL script, this tells Transfer to include all properties into the query result.

For example, to select the 'name' property of 'post.Post' as 'title', but also to select all properties on 'post.Post', you would use: <code>select post.Post.name as title, * from post.Post</code>

==== Aliasing Classes ====

<code>from ''class'' as ''classAlias''</code>

It is also possible to alias ''class'' declarations in your From statements. This is useful for writing complicated Where statements, and also for ease of development.

For example, to give the class 'post.Post' the alias of 'Post' for the query, simply use: <code>from post.Post as Post</code>

==== Selecting properties from aliased classes ====

<code>select ''classAlias''.''property'' from ''class'' as ''classAlias'' </code>

When aliasing ''class''es, you '''must''' refer to them via their ''classAlias'', and not via their full ''class''.

Thus, when selecting a specific property from an aliased ''class'', it is imperative that you preface the statement with the ''classAlias''

For example, when selecting the 'name' property on a 'post.Post' object in the TQL script which has been aliased to 'Post', the TQL must look like: <code>select Post.name from post.Post as Post</code>

==== Where statements with operators ====

<code> from ''class'' where ''class''.''property'' ( = | > | < | != | <> | <= | >= | like ) ( ''mappedParameter'' | ( ''class'' | ''classAlias'' ).''property'' ) </code>

TQL also has Where statements, in which you can place conditional statements to filter out data from your TQL queries.

For example, to get a list of Posts, with a filter on a 'like' condition: <code>from post.Post where post.Post like :name</code>. From there you are able to set the ''mappedParameter'' to a value using the TQL Query object, as described above.

==== Where statement with an IN statement ====

<code> from ''class'' where ''class''.''property'' [NOT] IN "(" ( ''mappedParameter'' | ''subSelectStatement'' ) ")" </code>

Where statements can utilise IN statements, in the same way that SQL can. IN statements can either take a ''mappedParameter'', or a TQL subselect statement.

For example, if your using a ''mappedParameter'' statement within your TQL script, it could look something like: <code>select from post.Post where post.Post.IDPost IN ( :idpostList )</code>. From there you would be setting the ''mappedParameter'' via the query object, most likely with the 'list' argument set to true.

You can also use a sub select within an IN statement. A sub select '''must''' have only a single property selected on it. For example: <code>from post.Post where post.Post.name IN ( select user.User.name from user.User )</code> will select all Posts with the same name as a User.

==== Where statement with a NULL statement ====

<code> from ''class'' where ''class''.''property'' IS [NOT] NULL </code>

Where Statements can also contains conditions against database NULL values. For example, to get all Posts where the Name of the post is NULL: <code>from post.Post where post.Post.name IS NULL</code>

==== Where statement with combinations of operators, NULL and IN statements ====

A combination of operator, NULL, and IN statements can be made, seperated by 'and' or 'or' boolean operators, as well as grouped with parenthesise.

For example:

<code><pre>
 	from
 		post.Post
 	where
 		post.Post.createdDate >= :createDate
 		AND
 		(
 			post.Post.body like :search
 			OR
 			post.Post.name like :search

 		)
</pre></code>

will select all Posts that were created past a certain date, and whose Name or Body is LIKE a certain search term.

==== Where statements with aliased classes ====

<code> from ''class'' as ''classAlias'' where ''classAlias''.''property'' ... </code>

In the same way that selecting ''properties'' from an aliased ''class'', in Where statement, if a ''class'' has been aliased, it '''must''' be referred to by it's ''classAlias'', rather than by it's full name.

For example: <code>from post.Post as Post where Post.name != :name</code> we have used the ''classAlias'' of 'Post' as appropriate in the Where statement.

==== Auto Joins ====

<code>from ''class'' [as ''classAlias''] [ join ''class'' [as ''classAlias''] ]*</code>

Since Transfer knows about the relationships between classes and therefore thier corresponding tables, TQL can be used to automatically build SQL join statement for you.

For example, if we assume that 'post.Post' has a ''onetomany'' relationship to 'post.Comments', we could join all posts to their comments simply by using the script: <code>from post.Post join post.Comments</code>

To continue that, if we wanted the Post joined with it's Comments and the User that created the Post, we could also have: <code>from post.Post join post.Comment join user.User</code>

==== Specific Join ====

<code> from ''class'' [as ''classAlias''] join ''class'' [as ''classAlias''] ON ( ''class'' | ''classAlias'' ).''composite'' [ ( and | or ) ( ''class'' | ''classAlias'' ).''composite'' ]* </code>

Sometimes when there are multiple relationships between ''classs''es, you will want to only specify a particular relationship, or set of relationships that you wish to join on. This is possible by specifiying the name of the composite element in a ON statement.

For example, if a 'post.Post' had relationships to two 'user.User's, one a the user that created it, and a user that the Post is about, we could specify that we only want the Author to be joined by saying:

<code><pre>
 	from post.Post as Post
 		join user.User 
 			ON Post.Author
</pre></code>

where 'Author' is the name of the ''manytoone'' composite element on the 'post.Post' object.

==== Manual Joins ====

<code> from ''class'' [as ''classAlias''] join ''class'' [as ''classAlias''] ON ( ''class'' | ''classAlias'' ).''property'' ( ( = | < | ... )... | IN... | IS [NOT] NULL... ) [ (AND|OR)...] </code>

Depending on your object configuration file, there may be no relationship for the 'join' to follow, in that case it is possible to specify joins in almost exactly the same way you do in SQL, with the properties of the configured ''class''es.

These conditions follow the same patterns as conditions in Where statements.

For example, to join all 'post.Post's with 'user.User' on their name property:

<code><pre>
 	from
 		post.Post as Post
 			join
 				user.User as Author
 					ON Author.name = Post.name
</pre></code>

==== Multiple Joins ====

Multiple joins on a single query may be executed, including Joins of different types, for example:


<code><pre> 
from 
 	post.Post as Post 
 	join 
 	post.Comments 
 	join 
 	user.User as User 
 		on Post.Author 
 	join
 	User.UserSetting as Setting
 		on User.settingKey = Setting.key
</pre></code>


==== Left and Right Outer Joins ====

<code> from ''class''<nowiki> [[ left | right ] outer] join </nowiki>''class'' </code>

It is also possible to do outer joins with TQL. By default, TQL performs INNER JOIN statements on a 'join', but you can specify an outer join as well. By default, TQL performs a LEFT OUTER JOIN, but you can specify a RIGHT OUTER JOIN as required.

For example, to do an outer join on Users with their Posts: <code>from user.User outer join post.Post</code>

==== Order By ====

<code> from ''class'' [as ''classAlias''] order by ( ''class'' | ''classAlias'' ).''property'' [ asc | desc ] [ , ( ''class'' | ''classAlias'' ).''property'' [ asc | desc ] ]* </code>

In the same way you can do Order By statements in SQL to order your rows in your query, you can also do so in TQL.

For example, to order a list of Users by their name: <code>from user.User order by user.User.name</code>

==== Not supported in TQL ====

* Aggregate functions
* SQL Functions
* Subselect in FROM statements.
* Subselect in SELECT Column statements.

[[Category:TQL]]