Preside selectData(): filtering data — sebduggan.uk
sebduggan.uk

Preside selectData(): filtering data

If you've read part one of this series, you know how to retrieve data from Preside objects using selectData(). But any web application will need you to refine the data down to a subset of the whole dataset, or just a simple record (think of a SQL where clause).

Simple filtering #

Every Preside data object has a unique id, and we can use this to retrieve a single record from the database:

cfc
film = presideObjectService.selectData(
	  objectName = "starwars_film"
	, id         = "9F049349-18C4-4ED6-848BC0A41F510821"
);

In practice, of course, the ID you pass in is most likely to be dynamic -- maybe defined in the URL or form parameters in a request from another page.

Selecting by id (the record's primary key) is a special case; if you want to filter the records on any other field or fields, you pass a struct of filters to the filter argument:

cfc
film = presideObjectService.selectData(
	  objectName   = "starwars_film"
	, filter       = {
		episode_id = 5
	  }
);

You can filter by multiple column values at the same time - if you do this using a filter struct, it will return records that match all the filter values, equivalent to SQL's where ... and .... In the next example, we'd are asking for all characters who are female and have brown hair:

cfc
characters = presideObjectService.selectData(
	  objectName = "starwars_character"
	, filter     = {
		  gender     = "female"
		, hair_color = "brown"
	  }
);

This method of filtering also allows you to match a column against one of a set of values (a SQL where ... in (...) clause). This is achieved by passing an array of values in the filter struct. So this next query would retrieve characters whose eyes are either brown, blue or black:

cfc
characters = presideObjectService.selectData(
	  objectName = "starwars_character"
	, filter     = {
		eye_color = [ "brown", "blue", "black" ]
	  }
);

More complex filters #

All the examples so far have been filtering for an "equals" match. But what if you need to do a greater than or non-equality comparison, or a complex grouping of conditions?

For this, you simply pass any valid SQL string to the filter argument instead of a struct:

cfc
theGoodFilms = presideObjectService.selectData(
	  objectName = "starwars_film"
	, filter     = "episode_id >= 4 and episode_id != 6"
);

Of course, usually the values will be dynamic, according to the requiredments of the request. To pass parameterised values into the filter, pass a struct of values in via the filterParams argument:

cfc
film = presideObjectService.selectData(
	  objectName   = "starwars_film"
	, filter       = "title != :title"
	, filterParams = {
		title = "A New Hope"
	  }
);

skywalkers = presideObjectService.selectData(
	  objectName   = "starwars_character"
	, filter       = "name like :name"
	, filterParams = {
		name = "%Skywalker"
	  }
);

In these cases, because the parameter name is the same as the field name (title, name), Preside is able to use the object definition to know what the SQL type is of the parameter (in both cases, it's varchar). However, often you will need to give the parameter a different name (for example, if we were executing the previous example with filterParams). You will now need to define explicitly the type of the parameter by setting its value to be a struct containing type (the SQL type of the field, as defined in your object, e.g. int or varchar) and value (the actual value to be parameterised):

cfc
theGoodFilms = presideObjectService.selectData(
	  objectName   = "starwars_film"
	, filter       = "episode_id >= :minEpisode and episode_id != :excludeEpisode"
	, filterParams = {
		  minEpisode     = { type="int", value=4 }
		, excludeEpisode = { type="int", value=6 }
	  }
);

Using the filterParams technique, you can still pass in arrays for SQL in ( ... ) queries:

cfc
characters = presideObjectService.selectData(
	  objectName   = "starwars_character"
	, filter       = "eye_color not in ( :eye_color )"
	, filterParams = {
		eye_color = [ "brown", "blue", "black" ]
	  }
);

characters = presideObjectService.selectData(
	  objectName   = "starwars_character"
	, filter       = "eye_color not in ( :colours )"
	, filterParams = {
		colours = { type="varchar", value=[ "brown", "blue", "black" ] }
	  }
);

WARNING! Never include variables directly in the filter string. These will be inserted directly into the generated SQL statement, and will leave you wide open to SQL injection attacks. Always pass dynamic variables in via filterParams.

Extra filters #

There are occasions when you will have a basic set of filters for a scenario, but you may also want in addition to apply other filters (some of which might be generated by helper methods). You could achieve this by appending all the filters to the main filter set, but the neater way is by using the extraFilters argument.

extraFilters is an array of filter conditions, each of which can be constructed using any of the variations discussed above. Each item in the array will be a struct with a filter value and an optional filterParams value.

cfc
characters = presideObjectService.selectData(
	  objectName   = "starwars_character"
	, filter       = { gender="female" }
	, extraFilters = [
		  {
			filter = { hair_color=[ "black", "brown" ] }
		  }
		, {
			  filter = "eye_color != :excludeColour"
			, filterParams = {
				excludeColour = { type="varchar", value="blue" }
			  }
		  }
		, getMyExtraCustomFilter()
	  ]
);

In the above example we see a regular filter, plus extraFilters containing a simple filter, a filter with explicitly defined parameters, and a filter returned from a separate method. All four of these filters will be combined into one SQL where clause.

Saved filters #

Preside allows you to pre-define filters to be used by your application in your Config.cfc file. These are actually of more use in other scenarios, such as filtering the records shown in an objectPicker form control, but they can also be used by selectData().

cfc
// application/config/Config.cfc

public void function configure() {
	// other configuration options...

	settings.filters.created_in_last_month = { filter = "datemodified >= date_add( now(), interval -1 month )" };
	settings.filters.modified_in_last_week = { filter = "datemodified >= date_add( now(), interval -3 day   )" };
}

The savedFilters are then passed in as an array, and will be applied in addition to any other filters you have defined.

cfc
recentVehicles = presideObjectService.selectData(
	  objectName   = "starwars_vehicle"
	, selectFields = [ "id", "name" ]
	, savedFilters = [ "created_in_last_month", "modified_in_last_week" ]
);

When defining saved filters in Config.cfc, they will generally not be dynamic; however, you can define a saved filter as a closure function which will return the filter configuration based on its internal logic. The closure will take two arguments, the first of which will be an empty struct of arguments, but the second will be the ColdboxController object, which will allow you to access various useful bits of information from the application and request.

Summary #

We now know how to select and filter data from single Preside objects, and have seen how flexible and powerful the data filtering is.

In the next part of this series I'll be looking at joins, and how easy Preside makes it to access data from multiple, related objects.