Filtering

There are 3 ways to implement server-side filtering:

Note, the server-side filtering of dhtmlxTreeGrid doesn't maintain open states.

URL manipulation

APPLICABLE TO:Grid, TreeGrid, Tree, Combo, Scheduler, DataView, Chart, Form

You can control which data was loaded into the component by specifying additional parameters in URL.

filtering by one field

grid.load("some.cfm?connector=true&dhx_filter[1]=mask");

With such a url, data will be taken with an additional rule.

WHERE field_for_column_1 LIKE %mask%

filtering by multiple fields

grid.load("some.cfm?connector=true&dhx_filter[1]=mask&dhx_filter[3]nother");

For all the components, except for dhtmlxGrid you should provide the exact field's name instead of the column's index.

Pay attention that such filtering is non-secure and if you need to hide some data - be sure to define such limitation on the server side, not as a part of URL.

In-header filter types

APPLICABLE TO: Grid, TreeGrid, Combo

To enable the server-side filtering you can use one of the following in-header filter types while configuring dhtmlxGrid/dhtmlxTreeGrid on the client side:

#connector_text_filter - a text filter. Retrieves values which contain a mask defined through a text field
#connector_select_filter - a select filter. Retrieves values which contain a mask defined through a dropdown list of possible values

mygrid.setHeader("Column A, Column B");
mygrid.attachHeader("#connector_text_filter,#connector_select_filter");

Text filter

The use of the text filter doesn't require any additional configuration code. Grid/TreeGrid will automatically send data about a newly entered text and filter server-side data using the %mask% pattern.

If you need to change the filtering pattern or implement a more advanced logic - the beforeFilter event should be used.

default filtering logic

<cffunction name="custom_filter">
    <cfargument name="filter_by">
        <!--- WHERE some_field LIKE 'value' --->
    <cfif NOT ArrayLen(ARGUMENTS.filter_by.rules)>
        <cfset ARGUMENTS.filter_by.add("some_field","value","LIKE")>
    </cfif>
</cffunction>
<cfset conn.event.attach("beforeFilter",custom_filter)>

redefined filtering logic

<cffunction name="custom_filter">
  <cfargument name="filter_by">
  <!--- change WHERE some_field LIKE '%value%' to the WHERE some_field gt 'value' --->
  <cfset var index = ARGUMENTS.filter_by.index("some_field")>
  <!--- there is a client-side input for the filter --->
  <cfif index> 
    <cfset ARGUMENTS.filter_by.rules[index]["operation"]=">">
  </cfif>
</cffunction>
<cfset conn.event.attach("beforeFilter",custom_filter)>

Through rules[index] you can refer to:

the name of a field (rules[index]["name"]="age")
the value of a field (rules[index]["value"]="30")
the type of an operation (rules[index]["operation"]=">")

Select filter

By default, grid/treegrid will use DISTINCT select against the related field, and fetch all possible options.

If you need to define a custom list of options you can use one of 2 ways:

a hardcoded list

<cfset param = structNew()>
<cfset param["1"] = "one">
<cfset param["2"] = "two">
<cfset param["3"] = "three">
<cfset grid.set_options("item_nm",param)>
<cfset grid.render_table("grid50","item_id","item_nm,item_cd")>

a list created on the base of a different table

<cfset filter1 = createObject(
    "component",
    "dhtmlxConnectors.OptionsConnector").init("#datasource#","MySQL")>
<cfset filter1.render_table("countries","item_id","item_id(value),item_nm(label)")>
<cfset grid.set_options("item_nm",filter1)>
<cfset grid.render_table("grid50","item_id","item_nm,item_cd")>

You can use both the render_table and render_sql methods for OptionsConnector object, the same as for any normal connector.

Beware that the name of the field, used in the select filter needs having the (value) alias.

Custom filters (using the beforeRender event)

APPLICABLE TO: Grid, Combo

By using the beforeRender event it's possible to define filtering rules as PHP code (doesn't work for dyn. modes).

<cffunction name="custom_filter">
    <cfargument name="data">
    <cfif ARGUMENTS.data.get_value("some") lt 0>
                <!--- not include in output --->
        <cfset ARGUMENTS.data.skip()>
    </cfif>
</cffunction>
<cfset conn.event.attach("beforeRender",custom_filter)>