DataMgr 2.5 Documentation: Insert Custom SQL

Insert Custom SQL

Although DataMgr defines several powerful ways to abstract the power of a datasource, sometimes you may want to include your own SQL in a query created by DataMgr. The "advsql" argument provides this ability

The "advsql" argument takes a structure with keys for each of the clauses of a SQL query.

Get Methods:

Update Methods:

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)>