Is it possible to get the cfquery tag to pull from two data sources? I know that I can do a query of query type query, but this doesn't quite accomplish what I need to do. Any suggestions?
I just want to start this off by saying I do NOT recommend doing this. The following is just an exploration of ideas.
As you already know, ColdFusion query of queries allows you to join queries taken from two different data sources. This is something that can't really be done within the CFQuery tag (without doing a query of queries). Or rather, it cannot be done (to the best of my knowledge) without employing some bad programming. And... I'm never one who is afraid to program badly, so let's give it a go.
As an experiment, let's say that we store file references in a database table "document". All entries in the document table should correspond to a physical file in the local directory. We can consider the database and the file system to be two different data sources, correct? After all, they can both return a query object. Let's say that you want to query for files from the database, but ONLY want to return files that exist in the local file system as well.
There are a number of ways that this can be done, but only one requires pulling from two data sources in a non-query of queries database call. To do this, we are going to call one data source, then use those results to create an in-memory table in the second data source. Then, we are going to join those tables together.
<!--- Query the file system for a list of files that should sync to the database. ---> <cfdirectory action="LIST" directory="#ExpandPath( './documents/' )#" name="qFile" /> <!--- Query our file query to get only the directory items that are FILES. Since we are doing this, might as well trim the column list. ---> <cfquery name="qFile" dbtype="query"> SELECT name FROM qFile WHERE LOWER( type ) = 'file' </cfquery> <!--- ASSERT: We are done querying our first data source, the file system. ---> <!--- Query the database for files. We only want to get files that also exist in the file system (ie. that also exist in the directory query we just ran). ---> <cfquery name="qDocument" datasource="..."> <!--- Define a table to hold the data from our first data source (the names of physical files). ---> DECLARE @file TABLE( server_name VARCHAR( 100 ), server_ext VARCHAR( 10 ) ); <!--- Insert files into temp file table. ---> <cfloop query="qFile"> INSERT INTO @file ( server_name, server_ext ) VALUES ( <cfqueryparam value="#ListFirst( qFile.name, '.' )#" cfsqltype="CF_SQL_VARCHAR" />, <cfqueryparam value="#ListLast( qFile.name, '.' )#" cfsqltype="CF_SQL_VARCHAR" /> ); </cfloop> <!--- ASSERT: At this point, we have created a temporary, in-memory table that has the essential data from our first data source query. Now, we can use this temp table to join to tables that exist in THIS data source. ---> <!--- Query for files from the database. ---> SELECT d.id FROM document d INNER JOIN @file f ON ( d.server_name = f.server_name AND d.server_ext = f.server_ext ) </cfquery>
So there you have it. We are basically building the required data from the first data source in the second data source and treating the second data source as having all the required information. This is the way to fake calling two data sources in one CFQuery tag (without using ColdFusion query of queries).
Now, as I said, I would NOT recommend doing this. It is:
Probably does not work in all (or even many) other database management systems (I test in MS SQL Server 2000).
It is slower than a standard query as we have to SEND much more data to the SQL server.
It is more likely to crash when your in-memory table gets to big. On my machine, I wasn't able to insert more than 1050 rows into the temp table. Not sure if this is a time-out issue or a limitation of memory size or the database itself.
There is no way this can scale well.
Ok, but enough with the CONS. There is a PRO to doing it this way: SQL server is more powerful than the ColdFusion query of queries. I LOVE query of queries, but SQL Server can simply do more stuff. If you absolutely need to do some higher level computation (or even a simple one like TOP) this might be a solution you can use (if query of queries or manually altered queries have been ruled out).
DISCLAIMER: This was only an example. I am not saying you SHOULD do this for this scenario.
Want to use code from this post? Check out the license.