DataFaucet ORM

Gateways

Simply put, a Gateway is a quick way of searching for data in your database. The object simply automates some common tasks related to creating list pages and search forms.

The basic gateway object provided with DataFaucet is initialized with the name of a table to search, a default sort order ("orderby") and a list of columns to return in the result queries. The table is the only required property. Once initialized, the Gateway provides you with three ways to fetch data from your table, via the methods Search, FindByFilters and dynamic "FindBy" methods (similar to gateways written in Ruby).

Search()Find records using a single user-input (think search engine i.e. Google)
FindByFilters()Find records using a collection of user-inputs (think "advanced search")
FindBy_* (dynamic)Find records using data for specific columns (think "lookup" i.e. "password retriever")

Getting a Gateway

A gateway object can be fetched from the datasource via the getGateway() method.

<cfscript>
  // get the datasource
  ds = request.DataFaucet.getDatasource(); 
  // get the gateway 
  productGateway = ds.getGateway(table="tblProducts",orderby="productprice desc"); 
</cfscript>

The getGateway method has these arguments

Argument Required Description
tabletrueWhere do I get my data?
orderByfalseHow do I order the results if the user didn't tell me?
columnsfalseWhich columns should I return? You may not want the entire content of a TEXT or CLOB field if it won't be displayed.
classNamefalseIncidates an alternative, extended gateway class to instantiate

The Duck!

The gateway object extends the duck, so that properties can be set or retrieved either using getValue() and setValue() or by using getXXX() and setXXX(). So for example, the default OrderBy value can be set with setValue("orderby","a,b,c") or with setOrderBy("a,b,c"). The same mechanism used to provide those methods (onMissingMethod) also underlies the Gateway's ability to provide dynamic FindBy methods.

Search()

This function simply takes a single input from the user and finds records where text content in the database matches their input. Moreover it applies the system's and/or keyword filtering ability automatically across all text columns in the gateway table. There is no configuration needed to add and/or keywords or to indicate which columns should be included in the search, all this is done for you automatically.

<cfscript>
  // get the datasource
  ds = request.DataFaucet.getDatasource(); 
  // get the gateway 
  productGateway = ds.getGateway(table="tblProducts",orderby="productprice desc"); 
  
  // get products where the name 
  // or the description contain the word 'chow' 
  qPetFood = productGateway.search("chow"); 
  
  // get products where the name or the description 
  // contain the word 'fish' or the word 'bird' 
  qFishOrBird = productGateway.search("fish or bird"); 
</cfscript>

FindByFilters()

This method provides a little more control to the user and since that means it's a little more complicated, it should be reserved for advanced-search type features. FindByFilters accepts a filters argument which is a structure and will filter the query results based on columns matching the structure keys. FindByFilter defaults to using the URL structure, so for simple purposes you can actually just use gateway.FindByFilter() and immediately type your column information directly into the URL.

By default the FindByFilters method also applies the and/or keyword feature to the query results, so if a user enters "chow or seed" in the ProductName input for example, it will return all records with either "chow" or "seed" in the ProductName column. The and/or keyword filters can be disabled for an individual query via the "andorkeywords" argument (FindByFilters(filters,AndOrKeywords=false)), or for the entire gateway with the "andorkeywords" property (gateway.setAndOrKeywords(false)).

<cfscript>
  // get the datasource
  ds = request.DataFaucet.getDatasource(); 
  // get the gateway 
  productGateway = ds.getGateway(table="tblProducts",orderby="productprice desc"); 
  
  // define some filters (this would normally be form or url variables) 
  filters = { 
    productname = 'chow or seed', 
    productcategory = 'petfood' 
  }; 
  
  // get the petfood query 
  qPetFood = productGateway.FindByFilters(filters); 
</cfscript>

FindBy_* (dynamic)

Dynamic filters in the gateway object allow you to easily perform searches based on specific columns. For example, to create a user lookup for a password retreival page, you might use gateway.FindBy_Email(form.email). This would automatically find any records in the database where the email column matches the supplied argument of form.email. You can also add additional filters with and/or via the method name like gateway.findBy_ProductName_Or_ProductCategory(). This actually is just a prettier way of doing something you can already do with the system's SQL library.

You can see in this example how the Gateway's dynamic FindBy method merely simplifies this query you could already create.

<cfscript>
  // get the datasource
  ds = request.DataFaucet.getDatasource(); 
  // get the gateway 
  productGateway = ds.getGateway(table="tblProducts",orderby="productprice desc"); 
  
  // get it from the gateway 
  qProduct = productGateway.findBy_Name_Or_Category(form.search,form.search); 
  
  // get it the "old fashioned" way 
  stmt = ds.getSelect(table="tblProduct",include="or"); 
  stmt.filter("Name",form.search); 
  stmt.filter("Category",form.search); 
  qProduct = stmt.execute(); 
</cfscript>

Customizing the Gateway

What you've seen up to this point are just the simplest ways to use the Gateway object. In some cases you might also want to extend the Gateway, creating your own component object for the purpose of adding even more flexibility.

As just one example of the reasons you might want to extend the gateway object, you could use inheritance to include additional filters that would apply to all queries returned by the statement for the purpose of security (as one example) or you could add listeners to the returned statement objects for the purpose of modifying the returned queries in ways that would be difficult or impossible with SQL.

<cfcomponent output="false" extends="datafaucet.system.gateway">
  <cfset setProperty("table","tblProduct") />
  
  <cffunction name="init" access="public" output="false">
  </cffunction>
  
  <cffunction name="getStatement" access="private" output="false">
    <!--- This gateway is for the public site - only show available products --->
    <cfreturn super.getStatement().filter("status","available") /> 
  </cffunction>
</cfcomponent>

Column Names

The names of columns returned by the gateway can be modified in much the same way that names of properties can be modified in an active record object. This is done by setting the properties "prefix", "badchars" and "staticcolumns".

<cfscript>
  // get the datasource
  ds = request.DataFaucet.getDatasource(); 
  // create a gateway object 
  gateway = ds.getGateway();
  
  // use the tblProduct table for data
  gateway.setValue("table","tblProduct"); 
  
  // remove the prefix "product" from column names
  gateway.setValue("prefix","product"); 
  
  // don't remove "product" from the productname or productid columns 
  gateway.setValue("staticcolumns","product_id,product_name"); 
  
  // remove underscores from column names - including id and name 
  gateway.setValue("badchars","_"); 
</cfscript>