Following on from my recent post where I detailed my approach to CFC organisation, I wanted to share some more details of my current CFML coding methodology.
Today I’m going to talk a little about database interaction.
Unless you’re taking advantage of ORM in your CF application, you will most likely have to spend a bit of time scaffolding your components with some standard CRUD functionality. Some developers like to use a single component to manage all database interactivity, but I tend towards creating functions for each individual component. The goal is to have a standardised set of code for CRUD functionality that serve as core private functions on which others can be layered for the needs of the app.
Below, I’m going to show you how I handle READ functionality and we’ll look at how to efficiently use a single function to select data, dealing with:
Some data
First off, we need some data to play with. For this exercise, I've created a very simple table with some test data in it.
Feel free to download the SQL if you'd like to play along.
n.b. I’m using Microsoft SQL Server Express 2014 and Lucee 4.5 for my examples. Everything should work in SQL Server 2008, CF9 and above.
I’m going to start by building up a very simple function that we can use to SELECT data from the table in our database.
<cffunction name="getRecords" access="public" returnType="query" output="false" hint="Queries the database for Games records">
<!--- Get records --->
<cfquery name="Local.qGetRecords" datasource="CFExamples">
SELECT
[Games].[ID]
, [Games].[DateCreated]
, [Games].[LastUpdated]
, [Games].[Title]
, [Games].[Machine]
, [Games].[ReleaseDate]
, [Games].[Publisher]
FROM [Games]
</cfquery>
<cfreturn Local.qGetRecords />
</cffunction>
As you’d expect, this will simply return all the data in our Games table.
Filtering
Let’s take a look at how to build record filtering into our functions. We’ll start by filtering by the most obvious criteria, the unique ID for the record.
First, we’ll add an argument to the function to accept an ID:
<cfargument name="ID" required="false" type="string" />
You may notice that I’ve chosen string as the argument data type, even though the ID will always be a numeric value. This is so that we can pass a list of IDs as an argument, and have our query return multiple matching rows.
Next we need to add the filters to our SELECT query.
WHERE 1 = 1
<cfif StructKeyExists(Arguments, "ID") AND ListLen(Arguments.ID) GT 1>
AND [ID] IN (<cfqueryparam cfsqltype="CF_SQL_BIGINT" value="#Arguments.ID#" list="true">)
<cfelseif StructKeyExists(Arguments, "ID") AND isNumeric(Arguments.ID) AND Arguments.ID GT 0>
AND [ID] = <cfqueryparam cfsqltype="CF_SQL_BIGINT" value="#Arguments.ID#">
</cfif>
As you can see, I’m using WHERE 1 = 1 as a dummy clause for my query. This will allow each conditional filter to begin with the word ‘AND’ without causing an error.
Using conditional statements in this way, we can dynamically construct our SQL query, whilst still maintaining security – don’t ever forget to use <cfqueryparam>!
We can use similar methodology to add other filters as applicable. Here's a more fleshed out version with a couple of examples:
<cffunction name="getRecords" access="public" returnType="query" output="false" hint="Queries the database for Games records">
<cfargument name="ID" required="false" type="string" hint="Filter by ID" />
<cfargument name="Title" required="false" type="string" hint="Filter by Title" />
<cfargument name="Machine" required="false" type="string" hint="Filter by Machine" />
<cfargument name="ReleaseDate" required="false" type="string" hint="Filter by release data - range separated by comma" />
<cfargument name="Publisher" required="false" type="string" hint="Filter by Publisher" />
<!--- Get records --->
<cfquery name="Local.qGetRecords" datasource="CFExamples">
SELECT
[Games].[ID]
, [Games].[DateCreated]
, [Games].[LastUpdated]
, [Games].[Title]
, [Games].[Machine]
, [Games].[ReleaseDate]
, [Games].[Publisher]
FROM [Games]
<!--- Dummy clause --->
WHERE 1 = 1
<!--- ID --->
<cfif StructKeyExists(Arguments, "ID") AND ListLen(Arguments.ID) GT 1>
AND [ID] IN (<cfqueryparam cfsqltype="CF_SQL_BIGINT" value="#Arguments.ID#" list="true">)
<cfelseif StructKeyExists(Arguments, "ID") AND isNumeric(Arguments.ID) AND Arguments.ID GT 0>
AND [ID] = <cfqueryparam cfsqltype="CF_SQL_BIGINT" value="#Arguments.ID#">
</cfif>
<!--- Title --->
<cfif StructKeyExists(Arguments, "Title") AND ListLen(Arguments.Title) GT 1>
AND [Title] IN (<cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="#Arguments.Title#" list="true">)
<cfelseif StructKeyExists(Arguments, "Title") AND Len(Arguments.Title)>
AND [Title] = <cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="#Arguments.Title#">
</cfif>
<!--- Machine --->
<cfif StructKeyExists(Arguments, "Machine") AND ListLen(Arguments.Machine) GT 1>
AND [Machine] IN (<cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="#Arguments.Machine#" list="true">)
<cfelseif StructKeyExists(Arguments, "Machine") AND Len(Arguments.Machine)>
AND [Machine] = <cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="#Arguments.Machine#">
</cfif>
<!--- Release date range --->
<cfif structKeyExists(Arguments, 'ReleaseDate') AND ListLen(Arguments.ReleaseDate) EQ 2>
AND [ReleaseDate] BETWEEN <cfqueryparam cfsqltype="cf_sql_timestamp" value="#GetToken(Arguments.ReleaseDate, 1, ',')#" /> AND <cfqueryparam cfsqltype="cf_sql_timestamp" value="#GetToken(Arguments.ReleaseDate, 2, ',')#" />
</cfif>
<!--- Publisher --->
<cfif StructKeyExists(Arguments, "Publisher") AND ListLen(Arguments.Publisher) GT 1>
AND [Publisher] IN (<cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="#Arguments.Publisher#" list="true">)
<cfelseif StructKeyExists(Arguments, "Publisher") AND Len(Arguments.Publisher)>
AND [Publisher] = <cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="#Arguments.Publisher#">
</cfif>
</cfquery>
<cfreturn Local.qGetRecords />
</cffunction>
<cfset Local.British_Home_Computing = getRecords(
Machine = 'ZX Spectrum,Amstrad CPC,Amstrad GX4000'
) />
<cfset Local.Nintendo_90s = getRecords(
ReleaseDate = '1990-01-01,1999-12-31'
, Publisher = 'Nintendo'
) />
<cfdump var="#Local#">
So now we can see our filtered datasets:
Pagination
There are plenty of different methods out there for paginating results of a query, but most of them require that we SELECT an entire recordset before filtering using CFML or sometimes JavaScript. I prefer to handle pagination directly in the database as it's much more efficient.
We can do this using a 'common table expression', which might be easier to think of as an RDBMS equivalent to 'query of queries'.
We can add a rownumber to the query and then query the results to select rows that are between our pagination parameters. Here's a basic example:
DECLARE
@PageSize INT = 5
, @CurrentPage INT = 1
;
WITH queryTable AS
(
SELECT TOP 100 PERCENT
[RowNumber] = ROW_NUMBER() OVER (ORDER BY [Games].[ID] ASC)
, [Games].[ID]
, [Games].[DateCreated]
, [Games].[LastUpdated]
, [Games].[Title]
, [Games].[Machine]
, [Games].[ReleaseDate]
, [Games].[Publisher]
FROM
[Games]
ORDER BY
[Games].[ID] ASC
)
SELECT
(SELECT COUNT(*) FROM queryTable) AS TotalRecords
, ID
, DateCreated
, LastUpdated
, Title
, Machine
, ReleaseDate
, Publisher
FROM queryTable
WHERE
[RowNumber] BETWEEN ((@CurrentPage - 1) * @PageSize + 1)
AND (((@CurrentPage - 1) * @PageSize) + @PageSize)
ORDER BY
[RowNumber]
This will give us a subset of our data as shown:
By adding this to our existing filterable function, we can filter the data and paginate our results, all before we ever get the CF query object. It's important to note that filtering should occurr before pagination, otherwise you'll only be filtering on the subset of data.
Here's how our function looks now:
<cffunction name="getRecords" access="public" returnType="query" output="false" hint="Queries the database for Games records">
<cfargument name="ID" required="false" type="string" hint="Filter by ID" />
<cfargument name="Title" required="false" type="string" hint="Filter by Title" />
<cfargument name="Machine" required="false" type="string" hint="Filter by Machine" />
<cfargument name="ReleaseDate" required="false" type="string" hint="Filter by release data - range separated by comma" />
<cfargument name="Publisher" required="false" type="string" hint="Filter by Publisher" />
<cfargument name="ResultsPerPage" default="25" type="numeric" hint="Number of results per page" />
<cfargument name="DisplayPage" default="1" type="numeric" hint="Which page to display" />
<!--- Get records --->
<cfquery name="Local.qGetRecords" datasource="CFExamples">
DECLARE
@PageSize INT = <cfqueryparam value="#Arguments.ResultsPerPage#" cfsqltype="cf_sql_integer" />
, @CurrentPage INT = <cfqueryparam value="#Arguments.DisplayPage#" cfsqltype="cf_sql_integer" />
;
WITH queryTable AS
(
SELECT TOP 100 PERCENT
[RowNumber] = ROW_NUMBER() OVER (ORDER BY [Games].[ID] ASC)
, [Games].[ID]
, [Games].[DateCreated]
, [Games].[LastUpdated]
, [Games].[Title]
, [Games].[Machine]
, [Games].[ReleaseDate]
, [Games].[Publisher]
FROM
[Games]
<!--- Dummy clause --->
WHERE 1 = 1
<!--- ID --->
<cfif StructKeyExists(Arguments, "ID") AND ListLen(Arguments.ID) GT 1>
AND [ID] IN (<cfqueryparam cfsqltype="CF_SQL_BIGINT" value="#Arguments.ID#" list="true">)
<cfelseif StructKeyExists(Arguments, "ID") AND isNumeric(Arguments.ID) AND Arguments.ID GT 0>
AND [ID] = <cfqueryparam cfsqltype="CF_SQL_BIGINT" value="#Arguments.ID#">
</cfif>
<!--- Title --->
<cfif StructKeyExists(Arguments, "Title") AND ListLen(Arguments.Title) GT 1>
AND [Title] IN (<cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="#Arguments.Title#" list="true">)
<cfelseif StructKeyExists(Arguments, "Title") AND Len(Arguments.Title)>
AND [Title] = <cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="#Arguments.Title#">
</cfif>
<!--- Machine --->
<cfif StructKeyExists(Arguments, "Machine") AND ListLen(Arguments.Machine) GT 1>
AND [Machine] IN (<cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="#Arguments.Machine#" list="true">)
<cfelseif StructKeyExists(Arguments, "Machine") AND Len(Arguments.Machine)>
AND [Machine] = <cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="#Arguments.Machine#">
</cfif>
<!--- Release date range --->
<cfif structKeyExists(Arguments, 'ReleaseDate') AND ListLen(Arguments.ReleaseDate) EQ 2>
AND [ReleaseDate] BETWEEN <cfqueryparam cfsqltype="cf_sql_timestamp" value="#GetToken(Arguments.ReleaseDate, 1, ',')#" /> AND <cfqueryparam cfsqltype="cf_sql_timestamp" value="#GetToken(Arguments.ReleaseDate, 2, ',')#" />
</cfif>
<!--- Publisher --->
<cfif StructKeyExists(Arguments, "Publisher") AND ListLen(Arguments.Publisher) GT 1>
AND [Publisher] IN (<cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="#Arguments.Publisher#" list="true">)
<cfelseif StructKeyExists(Arguments, "Publisher") AND Len(Arguments.Publisher)>
AND [Publisher] = <cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="#Arguments.Publisher#">
</cfif>
ORDER BY
[Games].[ID] ASC
)
SELECT
(SELECT COUNT(*) FROM queryTable) AS TotalRecords
, ID
, DateCreated
, LastUpdated
, Title
, Machine
, ReleaseDate
, Publisher
FROM queryTable
WHERE
[RowNumber] BETWEEN ((@CurrentPage - 1) * @PageSize + 1)
AND (((@CurrentPage - 1) * @PageSize) + @PageSize)
ORDER BY
[RowNumber]
</cfquery>
</cffunction>
This is a great way to get a paginated dataset, but it's worth noting that we'll still have to do some work with the results to display pagination controls in our views. In another post I'll show you how I generate pagination data with a separate function.
Sorting
As with filtering, it's important that we sort our dataset before we paginate.
It's easy enough to simply change the order directly in the SQL, but to expose different sorting options to our function we need to accept a fieldname as an argument and dynamically build our query. This is of course a dangerous thing to do, so we'll explicitly specify which fields can be used for sorting and validate against the list before building our query.
<cfargument name="SortField" default="ID" type="string" />
<cfargument name="SortDir" default="DESC" type="string" />
<!--- Set up allowed ordering fields --->
<cfset Local.AllowedSortFields = [
'ID'
, 'DateCreated'
, 'Title'
, 'Machine'
, 'ReleaseDate'
, 'Publisher'
] />
Now we can use some tenery operator magic to build our 'rownumber' statement:
[RowNumber] = ROW_NUMBER() OVER (ORDER BY [Games].[#ArrayFind(Local.AllowedSortFields, Arguments.SortField) ? Arguments.SortField : "ID"#] #ArrayFind(["DESC","ASC"], Arguments.SortDir) ? Arguments.SortDir : "DESC"#, [Games].[ID] DESC)
Let's put this into our function:
<cffunction name="getRecords" access="public" returnType="query" output="false" hint="Queries the database for Games records">
<cfargument name="ID" required="false" type="string" hint="Filter by ID" />
<cfargument name="Title" required="false" type="string" hint="Filter by Title" />
<cfargument name="Machine" required="false" type="string" hint="Filter by Machine" />
<cfargument name="ReleaseDate" required="false" type="string" hint="Filter by release data - range separated by comma" />
<cfargument name="Publisher" required="false" type="string" hint="Filter by Publisher" />
<cfargument name="ResultsPerPage" default="25" type="numeric" hint="Number of results per page" />
<cfargument name="DisplayPage" default="1" type="numeric" hint="Which page to display" />
<cfargument name="SortField" default="ID" type="string" />
<cfargument name="SortDir" default="DESC" type="string" />
<!--- Set up allowed ordering fields --->
<cfset Local.AllowedSortFields = [
'ID'
, 'DateCreated'
, 'Title'
, 'Machine'
, 'ReleaseDate'
, 'Publisher'
] />
<!--- Get records --->
<cfquery name="Local.qGetRecords" datasource="CFExamples">
DECLARE
@PageSize INT = <cfqueryparam value="#Arguments.ResultsPerPage#" cfsqltype="cf_sql_integer" />
, @CurrentPage INT = <cfqueryparam value="#Arguments.DisplayPage#" cfsqltype="cf_sql_integer" />
;
WITH queryTable AS
(
SELECT TOP 100 PERCENT
[RowNumber] = ROW_NUMBER() OVER (ORDER BY [Games].[#ArrayFind(Local.AllowedSortFields, Arguments.SortField) ? Arguments.SortField : "ID"#] #ArrayFind(["DESC","ASC"], Arguments.SortDir) ? Arguments.SortDir : "DESC"#, [Games].[ID] DESC)
, [Games].[ID]
, [Games].[DateCreated]
, [Games].[LastUpdated]
, [Games].[Title]
, [Games].[Machine]
, [Games].[ReleaseDate]
, [Games].[Publisher]
FROM
[Games]
<!--- Dummy clause --->
WHERE 1 = 1
<!--- ID --->
<cfif StructKeyExists(Arguments, "ID") AND ListLen(Arguments.ID) GT 1>
AND [ID] IN (<cfqueryparam cfsqltype="CF_SQL_BIGINT" value="#Arguments.ID#" list="true">)
<cfelseif StructKeyExists(Arguments, "ID") AND isNumeric(Arguments.ID) AND Arguments.ID GT 0>
AND [ID] = <cfqueryparam cfsqltype="CF_SQL_BIGINT" value="#Arguments.ID#">
</cfif>
<!--- Title --->
<cfif StructKeyExists(Arguments, "Title") AND ListLen(Arguments.Title) GT 1>
AND [Title] IN (<cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="#Arguments.Title#" list="true">)
<cfelseif StructKeyExists(Arguments, "Title") AND Len(Arguments.Title)>
AND [Title] = <cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="#Arguments.Title#">
</cfif>
<!--- Machine --->
<cfif StructKeyExists(Arguments, "Machine") AND ListLen(Arguments.Machine) GT 1>
AND [Machine] IN (<cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="#Arguments.Machine#" list="true">)
<cfelseif StructKeyExists(Arguments, "Machine") AND Len(Arguments.Machine)>
AND [Machine] = <cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="#Arguments.Machine#">
</cfif>
<!--- Release date range --->
<cfif structKeyExists(Arguments, 'ReleaseDate') AND ListLen(Arguments.ReleaseDate) EQ 2>
AND [ReleaseDate] BETWEEN <cfqueryparam cfsqltype="cf_sql_timestamp" value="#GetToken(Arguments.ReleaseDate, 1, ',')#" /> AND <cfqueryparam cfsqltype="cf_sql_timestamp" value="#GetToken(Arguments.ReleaseDate, 2, ',')#" />
</cfif>
<!--- Publisher --->
<cfif StructKeyExists(Arguments, "Publisher") AND ListLen(Arguments.Publisher) GT 1>
AND [Publisher] IN (<cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="#Arguments.Publisher#" list="true">)
<cfelseif StructKeyExists(Arguments, "Publisher") AND Len(Arguments.Publisher)>
AND [Publisher] = <cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="#Arguments.Publisher#">
</cfif>
ORDER BY
[Games].[ID] ASC
)
SELECT
(SELECT COUNT(*) FROM queryTable) AS TotalRecords
, ID
, DateCreated
, LastUpdated
, Title
, Machine
, ReleaseDate
, Publisher
FROM queryTable
WHERE
[RowNumber] BETWEEN ((@CurrentPage - 1) * @PageSize + 1)
AND (((@CurrentPage - 1) * @PageSize) + @PageSize)
ORDER BY
[RowNumber]
</cfquery>
<cfreturn Local.qGetRecords />
</cffunction>
And some examples:
<cfset Local.80s_By_Title = getRecords(
SortField = 'Title'
, SortDir = 'ASC'
, ResultsPerPage = 5
, ReleaseDate = '1980-01-01,1989-12-31'
) />
<cfset Local.80s_By_Date = getRecords(
SortField = 'ReleaseDate'
, SortDir = 'ASC'
, ResultsPerPage = 5
, ReleaseDate = '1980-01-01,1989-12-31'
) />
<cfset Local.90s_By_Publisher = getRecords(
SortField = 'Publisher'
, SortDir = 'ASC'
, ResultsPerPage = 5
, ReleaseDate = '1990-01-01,1999-12-31'
) />
This forms the basis of our read functionality. With this function in place, I tend to use a wrapper that passes parameters to this function and manipulates the results to handle pagination controls, logging, or anything else that might need to be done on each call.
I also add freetext searching using a separate function - hopefully I'll go into more detail on this in another post.
In the meantime, I hope what I've gone over here will be of help to someone out there!
Comments