Complex queries are complex enough, this simplifies when to add the AND’s and OR’s, and commas to a dynamic query.
As it happens in flexible web applications you end up wraping sections of your query in <cfif> tags. This can make things difficult in the where clause if you have to decide If the sql code is the first statement after the WHERE statement and as such, the AND can be omitted, or if it is the second statement, and the AND is required before.
The old school way is to use WHERE 1=1 at the beginning which allows you to always use AND at the beginning of your statement
Toung tied yet? This is an old-school example.
<cfquery ....>
SELECT *
FROM table_name
WHERE
1=1
<cfif form.check1 contains "a">AND column1 = 'a'</cfif>
<cfif form.check1 contains "b">AND column2 = 'b'</cfif>
<cfif form.check1 contains "c">AND column3 = 'c'</cfif>
</cfquery>
This works ok, but there is a more generic case where we have the same problem. Say we were creating an insert statement and for one reason or another didn’t want to use <CFINSERT>. In this insert we wanted to dynamically pick columns to be inserted based on submitted form data
<cfquery ...>
INSERT INTO table_name
( ID
<cfif isdefined("column1") >, column1</cfif>
<cfif isdefined("column2") >, column2</cfif>
<cfif isdefined("column3") >, column3</cfif>
)
values (
ID_SEQ.NEXTVAL
<cfif isdefined("column1")> ,'#column1#' </cfif>
<cfif isdefined("column2")> ,'#column2#' </cfif>
<cfif isdefined("column3")> ,'#column3#' </cfif>
)
</cfquery>
This works ok when we have a sequence, but what if we didn’t? we need to encapsulate the check to see if we need the comma so that our query stays easy.
In a nutshell thats what the component does. It checks to see if we need the comma (or AND) and shows it if we do.
<cfif not isdefined("query_helpercfc")><cfobject component="query_helper" name="query_helpercfc" ></cfif>
<cfquery ...>
#query_helpercfc.clear(",")#
INSERT INTO table_name
(
<cfif isdefined("column1") > #query_helpercfc.show()# column1</cfif>
<cfif isdefined("column2") > #query_helpercfc.show()# column2</cfif>
<cfif isdefined("column3") > #query_helpercfc.show()# column3</cfif>
)
#query_helpercfc.clear(",")#
values (
<cfif isdefined("column1")> #query_helpercfc.show()# '#column1#' </cfif>
<cfif isdefined("column2")> #query_helpercfc.show()# '#column2#' </cfif>
<cfif isdefined("column3")> #query_helpercfc.show()# '#column3#' </cfif>
)
</cfquery>
The first time query_helpercfc.clear(',') it just sets that we are working with commas, and clears it’s display flag.
The first time query_helpercfc.show() is called it does nothing; but ever time thereafter it displays the token which was set using the clear function.
You might like something else entierly; but this has made my queries easier to deal with.
Version 1.0