CMS content filtering and sorting on listing page when used custom queries

[Xiaofeng Liu]

Hi guys,

I'm having a major issue here and I have been struggling with it for days. I know probably I have to customize the core objectadmin tag. But I need someone to point me into the right direction of how.

Here is my problem:

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,departme­nt,displayOrder,bActive"

SortableColumns="datetimelastupdated,themeTitle,department,displayOrder,bAc­tive"
        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

--
Best regards,

Xiaofeng,^_^

[Blair McKenzie]

Unfortunately the objectadmin tag doesn't support sorting and filtering on custom queries. However, you can catch sorting and filtering above the objectadmin tag and incorporate them into your SQL yourself.

To handle sorting:
<cfif isDefined("session.objectadminFilterObjects[attributes.typename].sqlOrderBy")>
    <!--- do stuff with where clause in session.objectadminFilterObjects[attributes.typename].sqlOrderBy --->
</cfif>

To handle filtering:
<cfif isdefined("session.objectadminFilterObjects.#yourtypename#.stObject")>
   <!--- do stuff with values in session.objectadminFilterObjects[attributes.typename].stObject --->
</cfif>

Blair

--
Best regards,

Xiaofeng,^_^

--
You received this message cos you are subscribed to "farcry-dev" Google group.
To post, email: farcr...@googlegroups.com
To unsubscribe, email: farcry-dev+...@googlegroups.com
For more options: http://groups.google.com/group/farcry-dev
--------------------------------
Follow us on Twitter: http://twitter.com/farcry

[Xiaofeng Liu]

Hi Blair,

Thanks for the reply.

I just tried out sth I can do like this, which made the sorting working fine now!


<cfset orderBy = "dateTimeLastUpdated DESC, dateTimeCreated DESC">
<cfif StructKeyExists(FORM, "FARCRYFORMSUBMITBUTTONCLICKEDOBJECTADMIN") AND FORM.FARCRYFORMSUBMITBUTTONCLICKEDOBJECTADMIN EQ "sort">
    <cfif StructKeyExists(FORM, "SQLORDERBY") AND FORM.SQLORDERBY NEQ "">
        <cfset orderBy = FORM.SQLORDERBY>
    </cfif>   
</cfif>

<cfset qryArticles = objArticles.getArticlesCMS(

        lstDepartmentNames = stcLoggedInUserProfile.USERDEPARTMENT
    ,    lstStateIDs = stcLoggedInUserProfile.USERSTATE

    ,    orderBy = orderBy)>

<!--- set up page header --->

<admin:header title="Articles" />

<ft:objectAdmin
    title="Articles"
    typename="articles"
    ColumnList="status,title,createdby,lastupdatedby,datetimelastupdated,bActive"
    SortableColumns="title,datetimelastupdated,bActive"
    lFilterFields="title"
    sqlorderby="datetimelastupdated desc,datetimecreated desc"
    qRecordSet="#qryArticles#" />

<admin:footer />


I'm gonna try filtering based on what you suggested.

Thanks heaps.

Xiaofeng

--
Best regards,

Xiaofeng,^_^

[Blair McKenzie]

FarCry remembers the sort and filter across page requests - you should refer to those or users will be confused when the selected order/filter doesn't match the data.

Blair