compositeID's and manytomany
jeff
this:
<compositeid>
<manytomany name="EMPLOYEE_ID" />
<manytomany name="USER_ID" />
</compositeid>
<manytomany name="EMPLOYEES">
<link to="EMPLOYEES.EMPLOYEES" column="EMPLOYEE_ID"/>
</manytomany>
<manytoone name="USERS">
<link to="USERS.USERS" column="USER_ID"/>
</manytomany>
I checked the docs here:
http://docs.transfer-orm.com/wiki/Using_Composite_Keys.cfm
And it doesn't look like manytomany is an option, but I am new to
transfer so I thought I would ask.....
Mark Mandel
How would a m2m work on the DB side as a composite key?
Mark
jeff
Basically, I have a situation like this
TABLE - Sports
SportID
SportName
TABLE - Users
UserID
PeopleName
TABLE - People_Sports_Assocation
UserID
SportID
You may remember this from other classic jeff posts, such as
http://groups.google.com/group/transfer-dev/browse_thread/thread/971e97681b6e460f!
Grabbing this in SQL is a breeze, but I can't get the transfer XML to
work after hours of banging my head against the wall. so i guess a
better question should be:
1) What should be in the sports table object? Many to Many?
2) What should be in the People table object?
3) What should be in the association table if not a composite key?
Thanks again for your help!
> W:www.compoundtheory.com
Jennifer Larkin
I did not come here to get devoured by symbols of monarchy! -- Smirnov of the KGB, Casino Royale
Now blogging....
http://www.blivit.org/blog/index.cfm
http://www.blivit.org/mr_urc/index.cfm
Jon Messer
So if your User object has a OneToMany People_Sports_Association collection, then your People_Sports_Assocation object could/would use <parentonetomany class="package.User" /> as part of it's compositeid.
As to the other question, what should be in these objects depends on how you need to use them. There are a few different ways you could model this relationship :
You could have
1) Users ManyToMany Sports
2) Sports ManyToMany Users
3) Users OneToMany People_Sports_Association with either
3a) Sports OneToMany People_Sports_Association
or
3b) People_Sports_Association ManyToOne Sports
4) People_Sports_Association ManyToOne both User and Sport
or the inverse of 3 swapping User and Sport
You can't have ManyToMany on both objects (yet) and you can't choose M2M if there is anything other than the key fields in the link table, as they get cleared and recreated automatically when they get saved.
If there is information in the join table, and you typically go both from a Sport object to a User object but you also go from User objects to Sport objects, then you'd probably want to have User o2m People_Sports_Association AND Sport o2m People_Sports_Association.
In this case you'd have 2 parentonetomanies that make up the compositeid of People_Sports_Association
If there is information in the join table and you always (or almost always) go from user to sport, with sport objects being basically just "look up" type objects, then you'd probably want user o2m People_Sports_Association, and People_Sports_Association m2o sport
In this case you'd have one parentonetomany and one manytoone make up the compositeid.
There most certainly are variations I've left out here.
My suggestion is that if you don't have information in People_Sports_Association other than the key fields, use User ManyToMany Sport (no compositeid or join object required).
If there is information in People_Sports_Association other than foreign keys, then you have to choose one of the other models that closest matches how you will use it. My guess would be User o2m People_Sports_Association m2o Sports.
HTH
Mark Mandel
Under:
http://docs.transfer-orm.com/wiki/Using_Composite_Keys.cfm#Configuration
The class attribute found on the parentonetomany is the class name of
a object that has a onetomany composite element that points to the
current object configuration.
Let me know if that neesds to be clarified better.
Mark
Mark Mandel
I need to ask if you have looked at any of the same applications out there?
There are several, and they outline many of the relationships that you
can set up, and how you use them.
I highly recommend that you do have a look at the example
applications, as that is what they are there for.
Mark
jeff
All we have are the two ID columns in the association table, and thats
it, so what would my XML look like for the association table object,
just this:
<compositeid>
<property name="UserID" />
<property name="SportID" />
</compositeid>
And no manytomany in the association object?
But I would put the manytomany in the Users Table and the Sports
table, and link it back ot the association table? Is that correcT?
That seems weird since UserID is NOT actually an ID
On Jul 17, 1:01 pm, "Jon Messer" <sylvan.mes...@gmail.com> wrote:
> A parentonetomany signifies that a portion of the compositeid is the part of
> the compositeid that is being referenced by another (parent) objects
> OneToMany collection (via the foreign key).
>
> So if your User object has a OneToMany People_Sports_Association collection,
> then your People_Sports_Assocation object could/would use <parentonetomany
> class="package.User" /> as part of it's compositeid.
>
> As to the other question, what should be in these objects depends on how you
> need to use them. There are a few different ways you could model this
> relationship :
>
> You could have
> 1) Users ManyToMany Sports
> 2) Sports ManyToMany Users
> 3) Users OneToMany People_Sports_Association with either
> 3a) Sports OneToMany People_Sports_Association
> or
> 3b) People_Sports_Association ManyToOne Sports
> 4) People_Sports_Association ManyToOne both User and Sport
>
> or the inverse of 3 swapping User and Sport
>
> You can't have ManyToMany on both objects (yet) and you can't choose M2M if
> there is anything other than the key fields in the link table, as they get
> cleared and recreated automatically when they get saved.
>
> objects, then you'd probably want to have User o2m
> People_Sports_Association AND Sport o2m People_Sports_Association.
> In this case you'd have 2 parentonetomanies that make up the compositeid of
> People_Sports_Association
>
> up" type objects, then you'd probably want user o2m
> People_Sports_Association, and People_Sports_Association m2o sport
> In this case you'd have one parentonetomany and one manytoone make up the
> compositeid.
>
> There most certainly are variations I've left out here.
>
> My suggestion is that if you don't have information in
> People_Sports_Association other than the key fields, use User ManyToMany
> Sport (no compositeid or join object required).
>
> If there is information in People_Sports_Association other than foreign
> keys, then you have to choose one of the other models that closest matches
> how you will use it. My guess would be User o2m People_Sports_Association
> m2o Sports.
>
> HTH
>
> > As a follow up, I wasn't able to find in the documentation an explanation
> > of what parentonetomany actually does. Understanding that would probably
> > help us out a lot. Thanks
>
>
> >> It probably wouldn't :).
>
> >> Basically, I have a situation like this
>
> >> TABLE - Sports
> >> SportID
> >> SportName
>
> >> TABLE - Users
> >> UserID
> >> PeopleName
>
> >> TABLE - People_Sports_Assocation
> >> UserID
> >> SportID
>
> >> You may remember this from other classic jeff posts, such as
>
> >> !
Jon Messer
<object name="User" table="Users">
<id name="UserId" />
<manytomany name="Sport" table="Sports">
<link to="package.User" column="UserID"/>
<link to="package.Sport" column="SportID"/>
<collection type="array" />
</manytomany>
...
Jon Messer
<object name="User" table="Users">
<id name="UserId" />
<link to="package.User" column="UserID"/>
<link to="package.Sport" column="SportID"/>
<collection type="array" />
</manytomany>
jeff
In the Users Package:
<onetomany name="USER_SPORTS" lazy="true">
<link to="USER_SPORTS.USER_SPORTS" column="USERID"/>
<collection type="array">
<order property="USERID" order="asc"/>
</collection>
</onetomany>
In the association package:
<compositeid>
<parentonetomany class="USERS.USERS" />
<parentonetomany class="SPORTS.SPORTS" />
</compositeid>
<property name="USERID" type="numeric" column="USERID" />
<property name="SPORTID" type="numeric" column="SPORTID" />
And in the Sports Package
<onetomany name="USER_SPORTS" lazy="true">
<link to="USER_SPORTS.USER_SPORTS" column="SPORTID"/>
<collection type="array">
<order property="SPORTID" order="asc"/>
</collection>
</onetomany>
jeff
differently when doing a transfer.get(), and
1) pass in the compositeID's as a STRUCT!!!
2) prepend the compositeID with the word "parent"
For example
<cfset var composite = StructNew()>
<cfset composite.parentUSERS = 2 />
<cfset composite.parentSPORTS = 1 />
<cfset blah = transfer.get('USER_SPORTS.USER_SPORTS",composite)>
That would return one row with USERID 2 and SPORTID 1
Loony2nz
How do you do an update with a composite key?
I have a similar situation where I did the read of the record with the
composite key.
Now I have the record in a transfer object.
I do some manipulation on the object (do some sets).
Now how do I save the manipulated record?
I tried: transfer.save(blah)
But, it hung my system and stayed in an infinite loop. I just watched
my memory allocation climb endlessly.
Is there something special needed to do to the save method when saving
a composite key?
Jon Messer
Jon Messer
jeff
Jon is correct, transfer attempts the following query when the
properties exist:
INSERT INTO USER_SPORTS(SPORTID, USERID,SPORTID, USERID) VALUES (....
effectively doubling them up. We're rethinking this....
On Jul 18, 11:14 am, "Jon Messer" <sylvan.mes...@gmail.com> wrote:
> Having the USERID and SPORTID defined as properties is going to give you
> problems when you go to save (as in it won't work).
>
Jon Messer
jeff
a listbyproperty, or even write your own TQL to retrieve table data if
there are no properties for the object in transfer.xml?
So if I wanted to get all the SPORTID's the the USER_SPORTS table if
the USERID = 2, how would I do that?
On Jul 18, 2:49 pm, "Jon Messer" <sylvan.mes...@gmail.com> wrote:
> I would ask why do you need those as properties? You can grab the user
> object by userid and get to the related user_sport objects and by extension
> the related sports objects and the same is true for sport in the other
> direction. So what use case would require those ids as properties on the
> user_sports object, that could not be done by going through the parent user
> and sport objects?
>
Daniel Short
I have Users, which have onetomany Groups. My transfer definition
looks like this:
<package name="Users">
<object name="User" table="Users" decorator="_model.Users.UserDecorator">
<id name="ID" type="numeric" />
<property name="Username" type="string" column="Username" />
<property name="Password" type="string" column="Password" />
<property name="FirstName" type="string" column="FirstName" />
<property name="LastName" type="string" column="LastName" />
<property name="EmailAddress" type="string" column="EmailAddress" />
<property name="Confirmed" type="boolean" column="Confirmed" />
<property name="GUID" type="string" column="GUID" />
<property name="stamp" type="date" column="stamp" />
<property name="stampupdated" type="date" column="stampupdated" />
<onetomany name="Groups" lazy="true">
<link to="Groups.Group" column="UserID"/>
<collection type="array">
<order property="SortOrder" order="asc" />
</collection>
</onetomany>
</object>
</package>
<package name="Groups">
<object name="Group" table="Groups" decorator="_model.Groups.GroupDecorator">
<id name="ID" type="numeric" />
<!--
Don't set this as a property since it's part of a manytoone relationship
<property name="UserID" type="numeric" column="UserID" />
-->
<property name="GroupLabel" type="string" column="GroupLabel" />
<property name="SortOrder" type="numeric" column="SortOrder" />
<property name="stamp" type="date" column="stamp" />
<property name="stampupdated" type="date" column="stampupdated" />
<manytomany name="Items" table="GroupsItems">
<link to="Groups.Group" column="GroupID"/>
<link to="Items.Item" column="ItemID"/>
<collection type="array" />
</manytomany>
</object>
</package>
</package>
If I try to do something like this in a validate method it fails:
<cfset LOCAL.PropertyMap = {UserID = getUserID(), GroupLabel =
getGroupLabel()} />
<cfset LOCAL.groupnameCheck =
getTransfer().listByPropertyMap("Groups.Group", LOCAL.PropertyMap) />
It fails with:
The property 'USERID' could not be found in the object 'Groups.Group'
It seems to me that I should have implicit access to that property
since it's a foreign key in the table... If I add it as a property to
the Groups I get duplicate column name errors from SQL. It seems to me
wasteful that I always have to go get the parent object before I can
get the children. It also complicates the application as a whole since
intead of the two line bit of code above to check and see whether a
group name already exists for a user, I either have to get a user
object first, or I have to write TQL to do it instead.
Dan