DataFaucet ORM

Join Tables

Sooner or later it's going to happen. You're going to need data from two tables at the same time. The select object has a join() method to allow you to fetch data from related tables. The object created by join() is simply another Select object with all the same properties and methods.

The most important arguments to the join method are these:

PropertyDefaultDescription
tablen/aThe related table to join
requiredfalseboolean - indicates if data from the related table is required (true - inner join) or optional (false - left outer join)
columnn/athe name of the target column in the joined table - this argument is optional if you use foreign key constraints, which also means loose couplinge == good thing
referencesn/athe name of the source column in the table being adjoined - this argument is optional if you use foreign key constraints (see above)

Example:

<cfscript>
  // get the datasource
  ds = request.DataFaucet.getDatasource(); 
  // get a select statement 
  stmt = ds.getSelect("product.*, pc.categoryname","tblProduct product"); 
  
  // join the category table 
  stmt.join("productCategory pc",true,"productcategoryid","productcategoryid"); 
  
  // or if you use foreign key constraints (DO IT!) 
  stmt.join("productCategory pc",true); 
  
  // get the results 
  stmt.execute(); 
</cfscript>