Yesterday on the Model-Glue list, someone asked if it's possible to dynamically sort the resultset returned by the GenericList functionality in the Model-Glue:Unity beta. Yes, it is, but you have to write a wee bit of code to do it.

A bit of background:

The "GenericList" is what's known as a Generic Database Message. These are pre-defined messages within Unity that allow you to List, Edit, Commit, and Delete instances without writing a dedicated controller or listener function. It's possible to "configure" the behavior of each using message arguments.

For example, the default use of GenericList to list records from the WidgetType table looks like this:

That'll create a query named "WidgetTypeQuery" in the viewstate listing all records from the WidgetType table.

If you wanted to only list records where WidgetType.UserID was equal to the UserID in the viewstate (say, URL.UserID) and you wanted to list them in order of WidgetType.Name, and you wanted the resulting query to be named "UserWidgetTypeQuery," you'd use the following:

Ok, that's fine, but it's still pretty static. What if the query needed to do two subqueries, a join, and some aggregate functions?

Well, at that point, you're beyond the scope of what Reactor will automatically perform. You'd open up your WidgetTypeGateway.cfc, write some custom SQL in a method named something like "listWithComplexStuff", and use the "GatewayMethod" argument of GenericList to instruct it to use that method instead of the generic getByQuery() functionality that Reactor provides. It'd look like this:

That's OK, but it's now going to ignore the "orderColumn" argument because you're using your own custom SQL.

What's worth noting, though, is that any value listed in the criteria argument is passed to your custom method as a named argument. In other words, you'll have an argument named "arguments.UserID" available withing listWithComplexStuff.

Knowing this, we can revisit our original problem: how do we sort a GenericList based on something like a URL variable?

Now that we know about how criteria work with custom gateway methods, the answer's pretty simple: write a custom gateway method that has arguments like "orderBy" and "ascending" and uses Reactor's sort mechanism to perform the sorting.

If you want to cheat, here's the code (it should work in any Gateway, like widgetTypeGateway.cfc):

Note that this'll not just allow you to enter url variables like "orderBy" and "ascending," but also pass along additional criteria. With it, we could use the following message broadcast XML:

Now, hitting a URL like http://localhost/widget/?event=widgetType.list&orderBy=name&ascending=false&userId=1 will give us a list of WidgetType records where WidgetType.UserID = 1 sorted by WidgetType.Name in a descending manner.