DataMgr 2.2 Documentation: Insert Custom SQL

Insert Custom SQL

Although the "data" and "filters" arguments of the getRecords are very powerful, sometimes you will need even more flexibility for filtering records.

Similarly, while special fields and relation fields are very powerful ways to define columns in a data set, sometimes you will need even more flexibility. The "advsql" argument of the getRecords method provides all of these abilities and more.

The "advsql" argument takes a structure with any of the following keys:

Each of those keys can contain SQL.

So, for example a complicated field can be defined with the following code:

<cfset sAdvSQL = StructNew()>
<cfsavecontent variable="sAdvSQL.SELECT"><cfoutput>
CASE
WHEN EXISTS (
SELECT QuestionID
FROM answers
WHERE answers.QuestionID = questions.QuestionID
AND answers.PerformanceID = #Val(QuizID)#
--Logic here to indicate that chosen option value has changed
) THEN 1
ELSE 0
END isQuestionChanged
</cfoutput></cfsavecontent>

<cfset qQuestions = variables.DataMgr.getRecords(tablename="questions",advsql=sAdvSQL)>

Note that with both "data" and "filters", all of the arguments create "AND" clauses in the "WHERE" clause of a query. With advsql, it is possible to use parentheticals to allow "OR" arguments.

<cfset sAdvSQL = StructNew()>
<cfsavecontent variable="sAdvSQL.WHERE"><cfoutput>
(
isOver21 = 1
OR hasParentalApproval = 1
)
</cfoutput></cfsavecontent>

<cfset qUsers = variables.DataMgr.getRecords(tablename="users",advsql=sAdvSQL)>