There are 3 ways to implement server-side filtering:
Note, the server-side filtering of dhtmlxTreeGrid doesn't maintain open states.
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.
With such a url, data will be taken with an additional rule.
WHERE field_for_column_1 LIKE %mask%
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.
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:
mygrid.setHeader("Column A, Column B"); mygrid.attachHeader("#connector_text_filter,#connector_select_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.
<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)>
<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:
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:
<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")>
<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.
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).
Back to top
<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)>