I basically want to allow something like:
<cf_datatables datasource="#someDS#">
<cf_datatables_records>
SELECT
`someColumn1`,
`someColumn2`
FROM
`#someDB#`.`#someT#`
WHERE
`someColumn1` = <cfqueryparam value="#someValue#" cfSqlType="CF_SQL_INTEGER">
LIMIT
10
</cf_datatables_records>
</cf_datatables>
The cf_datatables_records custom tag (child) is a sub-query/recordset that will be used within the cf_datatables custom tag (parent). This would be the SQL result when executing the above:
SELECT
SQL_CALC_FOUND_ROWS *
FROM (
SELECT
`someColumn1`,
`someColumn2`
FROM
`#someDB#`.`#someT#`
WHERE
`someColumn1` = <cfqueryparam value="#someValue#" cfSqlType="CF_SQL_INTEGER">
LIMIT
10
) AS `base`
UNION (...)
WHERE ... GROUP BY ... ORDER BY ... LIMIT ... etc. (depends on the provided parameters)
Unfortunately ColdFusion parses the cfqueryparam tag before passing them to the custom tag resulting in:
Context validation error for tag cfqueryparam. The tag must be nested inside a cfquery tag.
It does work if I omit cfqueryparam but that's obviously not acceptable. So is parsing the queryparam.
Any idea how to solve this problem?
Okay, I just found out that I can include SQL instructions inside of cfquery without dealing with the native tag issue.
<cfquery datasource="#someDS#">
<!--- count possible records --->
<cfmodule dtArguments="#dtController.params#" template="datatables-processing-pre.cfm">
<!--- select records for the desired entity --->
SELECT
`someColumn1`,
`someColumn2`
FROM
`#someDB#`.`#someT#`
WHERE
`someColumn1` = <cfqueryparam value="#someValue#" cfSqlType="CF_SQL_INTEGER">
LIMIT
10
<!--- filter, order and paginate records --->
<cfmodule dtArguments="#dtController.params#" template="datatables-processing-post.cfm">
</cfquery>
The two includes complement the select dynamically.
Content of datatables-processing-pre.cfm:
SELECT
SQL_CALC_FOUND_ROWS *
FROM (
Content of datatables-processing-post.cfm:
) AS 'base'
WHERE
<cfswitch ...>
<defaultcase>
`someColumn3` > <cfqueryparam value="#someOtherValue#" cfSqlType="CF_SQL_INTEGER">
</defaultcase>
</cfswitch>
<cfif ...>
AND `someColumn4` LIKE <cfqueryparam value="#anotherValue#%" cfSqlType="CF_SQL_VARCHAR">
</cfif>
ORDER BY
<cfswitch ...>
...
</cfswitch>
...