Search

Wednesday, January 30, 2008

Using Generic Lists and SQL Server Views in Model-Glue

Model-Glue comes with some generic messages that you can use to manipulate data from your database. Generic messages are only available when you are using an ORM adapter with Model-Glue (such as Reactor or Transfer). In general, "messages" are how Model-Glue refers to requests to the server. As a very brief overview, messages are broadcast by the event handler and then handled by the defined Model-Glue controller. You can read more about this at The Model-Glue Event Lifecycle in Layman's Terms on Doug Boude's blog.

I find that I most often use the "ModelGlue.genericList" message which will return a ColdFusion query from the table specified. To use this feature in your event-handler, you simply add something like so under the "broadcasts":

<message name="ModelGlue.genericList">
  <!-- The name of your table -->
  <argument name="object" value="Users" />

  <!-- The name of query variable where the data will be stored -->
  <argument name="queryName" value="usersList" />
</message>

The "object" and "queryName" are the two required arguments you have to specify. However, you can also add an order by clause:
<message name="ModelGlue.genericList">
  <!-- The name of your table -->
  <argument name="object" value="Users" />

  <!-- The name of query variable where the data will be stored -->
  <argument name="queryName" value="usersList" />

  <!-- The column by which the data will be ordered -->
  <argument name="orderBy" value="userName" />
</message>

Or even a criteria to filter the data:
<message name="ModelGlue.genericList">
  <!-- The name of your table -->
  <argument name="object" value="Users" />

  <!-- The name of query variable where the data will be stored -->
  <argument name="queryName" value="usersList" />

  <!-- The column by which the data will be ordered -->
  <argument name="orderBy" value="userName" />

  <!-- The column by which the data will be filtered -->
  <argument name="criteria" value="userName" />
</message>
The "criteria" argument is the equivalent of using the "where" clause in your SQL query. It has to contain a name of a column already part of your table. It can also contain a hard coded value for the column value such as:
<!-- The column by which the data will be filtered -->
<argument name="criteria" value="userName=boyan" />


If a hard coded value is not specified for the "criteria" argument, Model-Glue looks for the data in the form or url scope. In case current example, it will look for variable with the name "userName" in the event scope and use it's value to set the value of the "criteria".
The last, somewhat advanced option in the "genericList" message is the "gatewayMethod" argument. In case your query is more complicated than a simple table lookup (such as many joins, various arguments and special processing), you can use the "gatewayMethod" argument to specify what cfc method Model-Glue should use to get the "genericList". The use this argument, the method has to reside in the model gateway created for the object specified under the "object" argument. In this example, the "gatewayMethod" has to exists in the "Users" gateway. Here is what a gateway generated by Reactor looks like:

<cfcomponent hint="I am the mssql custom Gateway object for the jobs object.  I am generated, but not overwritten if I exist.  You are safe to edit me."
	extends="usersGateway" >

</cfcomponent>

And here is a simple "gatewayMethod" to get a list of users based on a SQL Server stored procedure. The method takes a single numeric parameter called "showDeletedUsers" based on which it returns all users or only currently active users. Please note that this method is over simplified to show how the functionality works:
<cfcomponent hint="I am the mssql custom Gateway object for the jobs object.  I am generated, but not overwritten if I exist.  You are safe to edit me."
	extends="usersGateway" >

<cffunction name="getUsers" access="public" output="false" returntype="query">
	<cfargument name="showDeletedUsers" type="numeric" required="yes" />

	<cfset var usersQuery = queryNew("") />

	<cfstoredproc datasource="#_getConfig().getDsn()#" procedure="dbo.spGetUsers">
		<cfprocparam
		type="in" null="no" cfsqltype="cf_sql_int"
		dbvarname="showDeletedUsers" value="#arguments.showDeletedUsers#" />

		<cfprocresult name="usersQuery" resultset="1">
	</cfstoredproc>

	<cfreturn usersQuery />
</cffunction>

</cfcomponent>

To invoke this method through the "genericList", you simple use:
<message name="ModelGlue.genericList">
  <!-- The name of your table -->
  <argument name="object" value="Users" />

  <!-- The name of query variable where the data will be stored -->
  <argument name="queryName" value="usersList" />

  <!-- The column by which the data will be ordered -->
  <argument name="orderBy" value="userName" />

  <!-- The column by which the data will be filtered -->
  <argument name="criteria" value="showDeletedUsers=0" />

  <!-- The custom method to use when retrieving the data -->
  <argument name="gatewayMethod" value="getUsers" />
</message>

The last thing to remember is that you can use the "genericList" with SQL Server views and not just tables. Views are like a custom table that you have defined on the server. They can contain custom fields and data from multiple tables. So if you have a created a view called "vGetUsers" (instead of using the custom "gatewayMethod"), you can use in the "genericList" in the same manner as you would a table:
<message name="ModelGlue.genericList">
  <!-- The name of your view -->
  <argument name="object" value="vGetUsers" />

  <!-- The name of query variable where the data will be stored -->
  <argument name="queryName" value="usersList" />

  <!-- The column by which the data will be ordered -->
  <argument name="orderBy" value="userName" />
</message>
// //]]>