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
--
You received this message cos you are subscribed to "farcry-dev" Google group.
To post, email: [email protected]
To unsubscribe, email: [email protected]
For more options: http://groups.google.com/group/farcry-dev
--------------------------------
Follow us on Twitter: http://twitter.com/farcry
