Filtering and sorting not working when passed in custom query into formtools objectadmin

[Xiaofeng Liu]

Hi guys,

I'm having a major issue here. When I passed in a custom query to the
<ft:objectAdmin /> tag, all the sorting and filtering stopped working.
I can't really use sqlWhere and sqlOrderBy because the query I passed
in is quite complex which used joins and other syntax like where not
exists etc.

Below is my code in customadmin/customlists/themes.cfm:

<cfsetting enablecfoutputonly="true">

<cfimport taglib="/farcry/core/tags/formtools" prefix="ft" />
<cfimport taglib="/farcry/core/tags/admin/" prefix="admin" />

<cfset objThemes =
createObject("component","farcry.projects.#APPLICATION.projectDirectoryName#.packages.types.themes")>
<cfset objUserProfile =
createObject("component","farcry.core.packages.types.dmProfile")>

<cfset loggedInUserName = SESSION["dmprofile"]["USERNAME"]>
<cfset stcLoggedInUserProfile = objUserProfile.getProfile(userName =
loggedInUserName)>

<cfset qryThemes = objThemes.getThemesCMS(
lstDepartmentNames = stcLoggedInUserProfile.USERDEPARTMENT
, lstStateIDs = stcLoggedInUserProfile.USERSTATE
, orderBy = "department, displayOrder")>

<!--- set up page header --->
<admin:header title="Themes" />

<ft:objectAdmin
title="Themes"
typename="themes"

ColumnList="createdby,lastupdatedby,datetimelastupdated,themeTitle,department,displayOrder,bActive"

SortableColumns="datetimelastupdated,themeTitle,department,displayOrder,bActive"
lFilterFields="themeTitle,department"
sqlorderby="department,displayOrder"
qRecordSet="#qryThemes#" />

<admin:footer />

<cfsetting enablecfoutputonly="no">

The query I passed in is the type themes.cfc:

<cffunction name="getThemesCMS" access="public" returntype="query"
output="false">
<cfargument name="lstDepartmentNames" type="string"
required="false">
<cfargument name="lstStateIDs" type="string" required="false">
<cfargument name="orderBy" type="string" required="false">

<cfset var qryThemes = "">

<cfquery name="qryThemes" datasource="#APPLICATION.DSN#">
SELECT T.objectID
, T.themeTitle
, D.departmentName
FROM themes T
LEFT JOIN departments D ON T.department = D.objectID
WHERE 1 = 1
<!--- Themes for specific departments --->
<cfif StructKeyExists(ARGUMENTS, "lstDepartmentNames") AND
ARGUMENTS.lstDepartmentNames NEQ "">
AND (1 = 0
<cfloop list="#ARGUMENTS.lstDepartmentNames#" index="dept">
OR D.departmentName = <cfqueryparam cfsqltype="cf_sql_varchar"
value="#dept#">
</cfloop>
OR len(T.department) = 0)
</cfif>
<!--- Themes for target states --->
<cfif StructKeyExists(ARGUMENTS, "lstStateIDs") AND
ARGUMENTS.lstStateIDs NEQ "">
<cfif NOT ListFindNoCase(ARGUMENTS.lstStateIDs, "0")>
AND (T.objectID IN (
SELECT DISTINCT TS.parentid
FROM themes_aThemeStates TS
INNER JOIN states ST ON TS.data = ST.objectID
WHERE ST.id IN (#ARGUMENTS.lstStateIDs#)
OR ST.id = <cfqueryparam cfsqltype="cf_sql_tinyint" value="0">)

OR NOT EXISTS (
SELECT TS1.data
FROM themes_aThemeStates TS1
WHERE TS1.parentid = T.objectID
)
)
</cfif>
</cfif>
<cfif StructKeyExists(ARGUMENTS, "orderBy")>
ORDER BY #ARGUMENTS.orderBy#
</cfif>
</cfquery>

<cfreturn qryThemes>
</cffunction>

Any ideas how I can fix this?

Thanks in advance,

Xiaofeng

[Geoff Bowers]

On Tuesday, 17 April 2012 16:05:35 UTC+10, Xiaofeng Liu wrote:

I'm having a major issue here. When I passed in a custom query to the
<ft:objectAdmin /> tag, all the sorting and filtering stopped working.
I can't really use sqlWhere and sqlOrderBy because the query I passed
in is quite complex which used joins and other syntax like where not
exists etc.

You may have to build your own filtering interface.

-- geoff

http://www.daemon.com.au/

skype. gb.daemon

twitter. @modius