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 |
|---|---|---|
| table | true | Where do I get my data? |
| orderBy | false | How do I order the results if the user didn't tell me? |
| columns | false | Which columns should I return? You may not want the entire content of a TEXT or CLOB field if it won't be displayed. |
| className | false | Incidates 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>