DataMgr List Relations

DataMgr has had "list" relations for some time now, but I don't think I have yet done a good job of explaining them. Hopefully I can correct that today.

(What is DataMgr?)

So, let's start with this example:

employees

groups

regions

This presents use with several options for using a "list" relation.

One to Many

Alternately:

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.

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:

Or the Following setColumn() call:

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

Here are what each attribute indicates:

• type="list": indicates I want a list
• table="groups": Indicates that I want my column to contain a value from a field in the "groups" table
• join-table="employees2groups": Indicates that this is a many-to-many relationship stored in the "employees2groups" table
• field="GroupID": Indicates that I want to get a value from the "GroupID" field of the "groups" table
• local-table-join-field="EmployeeID": means that the "EmployeeID" field of the "employees" table should match a field in the "employees2groups" table
• join-table-field-local="EmployeeID": means the at the "EmployeeID" field of "employees2groups" should match a field in the "employees" table
• join-table-field-remote="GroupID": means that the "GroupID" field of "employees2groups" should match a field in the "groups" table
• remote-table-join-field="GroupID": means that the "GroupID" field of "groups" should match a field in the "employees2groups" table

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.

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.

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

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

Hopefully this provides a good explanation of list relations in DataMgr. If you have any questions, let me know.

DataMgr is open source and free for any use. Download 2.2 Beta from the DataMgr page on my site.