-
Notifications
You must be signed in to change notification settings - Fork 81
Query of Queries
Query of queries (QoQ) is a technique for re-querying an existing query without another trip to the database.
<!--- query a database in the normal manner --->
<cfquery name="sourceQry" datasource="mydsn">
SELECT
*
FROM
my_db_table
</cfquery>
<!--- query the above query *object*. (this doesn't make a call to the database.) --->
<cfquery name="newQry" dbtype="query"><!--- the dbtype="query" attribute/value enables QoQ -->
SELECT
*
FROM
sourceQry <!--- instead of a real database table name, use the variable name of the source query object --->
</cfquery>
The above example isn't very useful, because newQry
is a straight copy of the source query, but it demonstrates the two requirements of QoQ:
- The
dbtype="query"
attribute - A source query object name (e.g.,
sourceQry
) instead of table name in the FROM clause.
Let's say you have the following database query, myQuery
:
<cfquery name="myQuery" datasource="mydsn">
SELECT
Name, Age, Location
FROM
People
</cfquery>
You would now have a list of names, ages and locations for all the people in a query called myQuery
.
Say you want to filter out people under 18 and over 90, but you don't want to hit the database again:
<cfquery name="filteredQuery" dbtype="query">
SELECT
Name, Age, Location
FROM
myQuery
WHERE
Age >= 18
AND Age <= 90
</cfquery>
filteredQry
contains the desired records.
Railo uses its own SQL implementation for QoQ; when that fails, HSQLDB is tried.†
Railo's SQL implementation is a basic subset of ANSI92, but it is relatively fast. HSQLDB is a more complete SQL implementation, but it is slow, as compared to Railo's implementation.
Even though under the hood, Railo handles the fallback to HSQLDB automatically, it still can be useful to know what's possible with the fast Railo SQL implementation versus the slower, fallback HSQLDB SQL implementation.
At the time of this writing (Railo 4.2), the following were supported by Railo's SQL implementation:†
- <=
- <>
- =
- =>
-
-
=
- !=
- ALL
- AND
- AS
- BETWEEN x AND y
- DESC/ASC
- DISTINCT
- FROM
- GROUP BY
- HAVING
- IN ()
- IS
- IS NOT NULL
- IS NULL
- LIKE
- NOT
- NOT IN ()
- NOT LIKE
- OR
- ORDER BY
- SELECT
- TOP
- UNION
- WHERE
- XOR
TODO: Flesh this out.
This is the fallback for when Railo's SQL implementation can't handle the QoQ syntax. See the HSQLDB documentation for details.
- † Railo Google Groups Post: SQL syntax supported by query-of-queries?
- Getting to know Railo Server
- Railo Server features & specifications
- Getting started with Railo Server
- Installation & configuration
- Railo Server Versions
- Developing with Railo Server
- Deploying Railo Server apps
- Managing Railo Server apps
- Railo Server Extensions
- Useful resources & further reading
- Developing & debugging Railo Server
- FAQs