DataMgr 2.5 Documentation: Lists

Lists

List Relation Fields allow you to automate the handling of many-to-many relationships using lists. This essentially automates the saveRelationList() method with the added benefit that it brings the list into the recordset returned by getRecords().

You can filter by this value as well, limiting the recordset to records that have at least one of the list items given (so the longer the list you pass in to this key, the more results you are likely to get).

Attributes/Keys:

Let's start with this example:

EmployeeID RegionID FirstName LastName
1 1 Scott Summers
2 2 Jean Grey
3 3 Ororo Munroe
4 4 Peter Nicholas
5 1 Robert Drake
6 2 Henry McCoy
7 3 Kurt Wagner
8 4 Charles Xavier

groups

GroupID GroupName
1 Blue
2 Gold
3 Red

regions

RegionID RegionName
1 North
2 East
3 West
4 South

One to Many

For example, I might want a list of all of the EmployeeIDs associated with a given region. For that, I could use the following XML (see LoadXml):

<table name="regions">
   <field ColumnName="RegionID" CF_Datatype="CF_SQL_INTEGER" PrimaryKey="true" Increment="true" />
   <field ColumnName="RegionName" CF_Datatype="CF_SQL_VARCHAR" Length="50" />
   <field ColumnName="employees">
      <relation
         type="list"
         table="employees"
         field="EmployeeID"
         join-field-local="RegionID"
         join-field-remote="RegionID"
      />
   </field>
</table>

Alternately: 

<cfset sRelation = {
   type="list",
   table="employees",
   field="EmployeeID",
   join-field-local="RegionID",
   join-field-remote="RegionID"
}>
<cfset DataMgr.setColumn(tablename="regions",columnname="employees",Relation=sRelation)>

This simply references the "EmployeeID" field from the "employees" table where the value of the field indicated in "join-field-local" in the "regions" table ("RegionID") matches the value of the fields indicated in the "join-field-remote" ("RegionID") in the "employees" table.

Now, because both the "join-field-local" and "join-field-remote" values matched, I could have just used on "join-field" attribute as a shortcut, but I will stick with the more verbose syntax for clarity.

If I call DataMgr.getRecords("regions"), I will get back a query with an "employees" column that will have a comma delimited list with the values of every "EmployeeID" from a row where the "RegionID" of the "employees" table matches the "RegionID" for that record in the "region" table.

<cfset qEmployees = DataMgr.getRecords(tablename="regions")>
RegionID RegionName employees
1 North 1,5
2 East 2,6
3 West 3,7
4 South 4,8

The table in the database wouldn't have a "groups" field, but it would still be part of the query returned from that table by DataMgr.

Many to Many

I also might want a list of all of the GroupIDs associated with a given employee. For that, I could use the following XML:

<table name="employees">
   <field ColumnName="groups">
      <relation
         type="list"
         table="groups"
         join-table="employees2groups"
         field="GroupID"
         local-table-join-field="EmployeeID"
         join-table-field-local="EmployeeID"
         join-table-field-remote="GroupID"
         remote-table-join-field="GroupID"
      />
   </field>
</table>

Or the Following setColumn() call: 

<cfset sRelation = {
   type="list",
   table="groups",
   join-table="employees2groups",
   field="GroupID",
   local-table-join-field="EmployeeID",
   join-table-field-local="EmployeeID",
   join-table-field-remote="GroupID",
   remote-table-join-field="GroupID"
}>
<cfset DataMgr.setColumn(tablename="employees",columnname="groups",Relation=sRelation)>

Then a getRecords() call on the employees table can include the "groups" column.

<cfset qEmployees = DataMgr.getRecords(tablename="employees",fieldlist="EmployeeID,FirstName,LastName,groups")>
EmployeeID FirstName LastName groups
1 Scott Summers 1,3
2 Jean Grey 1,2
3 Ororo Munroe 2
4 Peter Nicholas 2
5 Robert Drake
6 Henry McCoy 1,3
7 Kurt Wagner
8 Charles Xavier 1,2,3

Here are what each attribute indicates:

The fields marked with red balloons indicate the One-to-Many example, while the fields with the blue balloons indicate the Many-to-Many example.

Since this list relation uses a join-table, the value of the field can be set when saving a record in the "employees" table.

<cfset sData = {EmployeeID=3,groups="1,2}>
<cfset Datamgr.saveRecord("employees",sData)>

This will add or remove records to the "employees2groups" table so that the value of the groups field is "1,2".

If I wanted to get a list of the "GroupName" fields from the "groups" table for groups associated with a given user, I could do that as well.

<table name="employees">
   <field ColumnName="groupnames">
      <relation
         type="list"
         table="groups"
         join-table="employees2groups"
         field="GroupName"
         local-table-join-field="EmployeeID"
         join-table-field-local="EmployeeID"
         join-table-field-remote="GroupID"
         remote-table-join-field="GroupID"
      />
   </field>
</table>
<cfset qEmployees = DataMgr.getRecords(tablename="employees",fieldlist="EmployeeID,FirstName,LastName,groupnames")>
EmployeeID FirstName LastName groupnames
1 Scott Summers Blue,Red
2 Jean Grey Blue,Gold
3 Ororo Munroe Gold
4 Peter Nicholas Gold
5 Robert Drake
6 Henry McCoy Blue,Red
7 Kurt Wagner
8 Charles Xavier Blue,Gold,Red

Just like when saving the "groups" field, a value can be passed in to the "groupnames" field.

<cfset sData = {EmployeeID=3,GroupNames="Gold,Blue"}>
<cfset Datamgr.saveRecord("employees",sData)>

Note that this will only work if a join-table is included in the list relation.

Note on bidirectional:

If you have a self-referential many-to-many relationship, you can use the "bidirectional" key to indicate if each relationship should be represented in both directions. A self-referential many-to-many relationship is one in which the local (main) table is the same as the remote (related) table and a join-table is in use.

Examine, for example, products that can each have multiple related products. If product A has product B as a related product does that mean that product B should automatically have product A as a related product? If so, set bidirection="true". If not, leave it alone (it defaults to false).