Introduction to Relation Fields

I added relation fields to DataMgr in version 2.0. Upon receiving a question about them by email, I noticed that I haven't ever written a good introduction about how to use them (though I have mentioned them several times).

What is DataMgr?

A little review, in case you aren't familiar with DataMgr. It is a data-access layer which handles CRUD functionality as well as Active Schema (if you choose) without generating any code. For more information read the DataMgr 2.1 announcement.

Instantiating DataMgr

The examples assume that you have DataMgr instantiated into Application scope. To do this, you can use the following code:

<cfif NOT StructKeyExists(Application,"DataMgr")>
    <cfset Application.DataMgr = CreateObject("component","DataMgr").init(mydsn,"MSSQL")>

This instantiates DataMgr against the datasource indicated in the "mydsn" variable using a SQL Server database. Other databases are available as well (currently Access, MySQL, PostGreSQL, SQL Server).

What is a Relation Field? 

A relation field acts as though it is a field in a table even though it does not exist in that table in the database. Instead, it references another field and DataMgr makes it act like it is a real field.

For example, a products table:


is related to a categories table:


If I always want to know the category name for any product, I can create a relation field to hold that value in a column named "Category".

To do that, I need to define the relation field with DataMgr (code examples assume DataMgr is instantiated into Application.DataMgr):

sRelation = StructNew();
sRelation["type"] = "label";
sRelation["table"] = "Categories";
sRelation["field"] = "CategoryName";
sRelation["join-field"] = "CategoryID";

I am passing a Relation structure to the setColumn method of DataMgr. This method can be used to ensure the existence of a column in a table, to set a special behavior on a column, or to create a relation field (as in this example). The keys that I am using in this example are as follows:

  • type: The type of relation field (this example is a "label" relation field, but DataMgr has other types as well).
  • table: The table from which DataMgr should get the value for the relation field.
  • field: The field from which DataMgr should get the value for the relation field.
  • join-field: The name of the field that should match from each table. The join-field key is really a short-cut for the following keys (used when the value of each matches):
    • join-field-local: The name of the field in the main table ("products" in this example) whose value should match that of the join-field-remote field in the related table ("categories" in this example).
    • join-fiel-remote: The name of the field in the remote table ("categories" in this example) whose value should match that of the join-field-local field in the local table ("products" in this example).

Now when I get the records from the "Products" table, DataMgr will also return a "Category" column with the value of the CategoryName field for the product's category.

<cfset qProducts = Application.DataMgr.getRecords("products")>

I can also filter by the category name if I want. So, if I want only products for the category named "Appliances":

<cfset sData = StructNew()>
<cfset sData["Category"] = "Appliances">
<cfset qProducts = Application.DataMgr.getProducts("Products",sData)>

I could even pass "Category" into DataMgr when saving a product to give the product the appropriate CategoryID for that category. I can also include (or remove) the Category field from the "fieldlist" argument of getRecords() to indicate whether it should be included in the result.

All of this behavior works as though the "Category" column were part of the "Products" table without that column actually existing in the table.

The above example is of a relation type of "label". Other relation field types work similarly. The types of relation fields currently available (as of DataMgr 2.1) are:

  • Aggregates (avg,count,max,min,sum)
  • Labels (label)
  • Concatinations (concat)
  • Lists (list)
  • Custom (custom)

The DataMgr documentation covers the other types of relation fields in detail (as well as more expanded options for their use). Note that as of DataMgr 2.1, relation fields can not only get data from other fields but from other relation fields as well (excepting "list" relation fields).

If you have any questions about how to use relation fields, please let me know.

DataMgr is open source and free for any use. It can be downloaded from RIAForge.

Related Blog Entries

Comments (Comment Moderation is enabled. Your comment will not appear until approved.)
Will dataMgr support the new "lite" database that ships with CF8?
# Posted By John Farrar | 7/31/07 3:06 PM

Not yet, but thanks for the reminder.

I have a local test of Derby support running, but I haven't tested it against the final release or cleaned it up (facing a deadline at the moment). I hope to finish it up and have it out in beta soon.
# Posted By Steve Bryant | 7/31/07 3:25 PM
Steve, I'm here to give you kudos! Thanks for your time with DataMgr. DataMgr is a time saver and code saver for our applications. Cheers.
# Posted By Marco Antonio | 7/31/07 9:36 PM
Thanks Marco Antonio!
# Posted By Steve Bryant | 8/1/07 2:12 PM
While I've been enjoying learning SQL, I'm amazed by what DataMgr does and am now using it to convert a site to it. However, I also want to now add a feature to my site and want to know if this is possible using DataMgr. Any suggestions would be appreciated.

I'm using a join table, "Products_Pages", for a content management feature, where multiple Products can appear on multiple Pages within the site. I would like to also store Sort Order so that we can control the order that Products appear on a particular Page. Is this possible using DataMgr?

PageID   ProductID   Sorter
1   22      1
1   34      3
1   24      2
2   22      2
2   9      3
2   16      1

My tests to insert the Sort order to this join table, with its combined primary key, using 'updateRecord' and 'saveRelationList', resulted in errors.
# Posted By Steve Spellman | 9/14/07 9:14 PM

Great question. I don't have a relation field set up to handle sorting in exactly the way that you want (though that is certainly a feature worth exploring).

I do, however, have a saveSortOrder method that should do what you want. Here is a link to the documentation for the method as well as to a video presentation I made a while back about its use.

Let me know if you have any more questions or suggestions.
# Posted By Steve Bryant | 9/15/07 12:59 PM
Hi Steve, maybe just one more question...

The 'saveSortOrder' method gives me the error message below:
"This method can only be used on tables with exactly one primary key field."

Do you have any suggestions on how I could continue to make use of the 'SaveRelationList' method (which requires the two-field primary key) for this join table, yet still add a sort feature for the site user. A separate web page would be okay, I'd just like to use DataMgr for it if possible. Thanks!
# Posted By Steve Spellman | 9/15/07 11:54 PM
BlogCFC was created by Raymond Camden. This blog is running version 5.8.001.