Grouping MySQL statements


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

Leave a Reply