Introducing Filters
I was working a project recently that required me to filter records by a date range. This represented a rare case where my use of DataMgr seemed to make my life more difficult rather than easier.
I had already set the site up to use relation fields (saving me hours of time), so I wasn't eager to scrap my use of DataMgr for this one hurdle (the advice I would generally give any time someone says DataMgr might add work).
DataMgr allows filtering by any field by passing in a structure of keys (in the "data" argument of the getRecords method) matching the names of the fields. This filtering, however, is only based on equality. I could also use the "advsql" argument to pass in SQL for a WHERE clause. This would work, but if the field by which to filter is a relation field than this would require passing in the SQL for the relation field.
I didn't want to give up the functionality of the relation fields. I could have used DataMgr's getFieldSelectSQL() to get the SQL for each each field, but that seemed like a lot of trouble.
So, it seemed like the ideal time to add a feature that had been on my list for some time. Hence, filters were born.
To use filters, pass in an array to the "filters" argument. Each item of the array should be a structure with the following keys:
- field (required): The name of the field to filter (can be a real field or a relation field).
- value (required): the value by which to filter.
- operator (optional): The operator by which to filter. Uses "=" if nothing else is passed in.
- possible values: =,>,<,>=,<=,LIKE,NOT LIKE,<>
This feature allowed me to filter records where the AppliedDate was between January 1, 2007 and January 16, 2007:
<cfset sFilter = {field="AppliedDate",operator=">=",value="1/1/2007"}>
<cfset ArrayAppend(aFilters,sFilter)>
<cfset sFilter = {field="AppliedDate",operator="<=",value="1/16/2007"}>
<cfset ArrayAppend(aFilters,sFilter)>
<cfset qApplicants = Application.DataMgr.getRecords(tablename="applications",filters=aFilters)>
This code works just the same if "AppliedDate" is an actual field in the "applications" table or if it is a relation field defined for that table.
Now records can be filtered in DataMgr using operators other than equality.
Filters are available in DataMgr 2.2 Alpha. DataMgr is open source and free for any use.
how can i specify logic operators, i.e. AND OR etc, for more than 1 condition in a filter?
regards
salvatore
Good question. I should have specified that the filters (like the data arguments) are all "AND" clauses. The problem with allowing "OR" as well is that it would then require an indication of precedence - something parenthesis handle quite well in SQL.
I would recommend using the "advsql" argument for that.
All of that having been said, I would certainly be open to suggestions for a syntax that handle that.
you can surely add in the array element of the filter another param corrisponding to the logic operator; another: can you give me a little esplanation aboutadvsql use or
suggest an article about this?
regards
salvatore
Adding an "AND/OR" choice to the filter would be easy. Once you have the ability to mix operators, however, assigning precedence order becomes essential (for which parenthesis are used in SQL). I can't think of a syntax for that in filters that would be as easy.
Here is an entry on advsql:
http://www.bryantwebconsulting.com/blog/index.cfm/...
If you have any more thoughts or questions, let me know.
i've read again this post and i'd ask if is it possible to add the IN operator in filters.
we often have a form with a lot of checkbox to select items, so the IN operator can be very usefull.
regards
It looks like I left them out of the list here (maybe I hadn't added them in the Alpha), but IN operators are supported as of DataMgr 2.2.
http://www.bryantwebconsulting.com/docs/datamgr/fi...
Sorry for any confusion.