In one of my applications that I am working on I need to grab a person’s profile, check to see if they have any documents to approve and/or review. Originally I was using multiple query calls to perform this task (one for the profile, another to check for approvals and the third for the reviews). The setup for our production server does not allow to us the option to use multiple quires in one statement – normally separated by a “;” – which I can use in my personal Railo server. To think of it I am not sure if ColdFusion 8+ has that option, I need to dig into the configuration to find out.
Well anyway, my goal is to reduce the number of calls going out to our production db so I started tinkering with the SQL query. I’ve done basic sub-queries before. Example:
SELECT profileid, firstname, lastname, emailaddress FROM application_profile WHERE profileid IN ( SELECT applicationprofile_profileid FROM login_table WHERE dateattempted > '2012-01-01 08:45:00' )
What I have never done was to stick query results into another query’s column. I wanted to get my three separate queries into one nicely packaged one. Mind you I am aware that you do lose performance as you pile on sub-queries, but for this instance I’m only grabbing one record and I regain performance by reducing the hits to the db. — At least in my current environment, the test showed a positive result. Please test your performance if you try to do something similar….
<cfquery name="qApplicationProfile" datasource="#variables.dsn#"> SELECT DISTINCT profileid, organizationid, positionid, preferred_name, lastname, emailaddress, moduleid ( SELECT ( ( SELECT count(documentid) FROM documents WHERE approver_profileid = <cfqueryparam value="#Arguments.ProfileID#" cfsqltype="cf_sql_integer" /> ) + ( SELECT count(connectionid) FROM documents_to_reviewers WHERE reveiwer_profileid = <cfqueryparam value="#Arguments.ProfileID#" cfsqltype="cf_sql_integer" /> ) ) > 0 ) AS hasDocumentsToReview FROM application_profile LEFT JOIN application_moduleaccess ON (application_profile.profileid = application_moduleaccess.applicationprofile_profileid AND activestatus = 1) WHERE profileid = <cfqueryparam value="#Arguments.ProfileID#" cfsqltype="cf_sql_integer" /> AND profileisactive = 1 </cfquery>
There are some cool quick db update statements that my friend found and will be blogging about shortly. Check out ColdFusion Beyond