You're probably going to want to do a join at some point and have the name of both of those users in the joined query.
To do that you might try what I have done below.
It seems like a lot of extra xml, but it works. And the aliases can be used with any table that has a CreatedBy_User.
A lot of our tables have 4 identical fields (dateCreated, dateModified, CreatedBy_UserID, ModifiedBy_UserID).
So, I'm trying to get a list of all the Campaigns including the CreatedBy_User and the ModifiedBy_User.
I have set up aliases in the reactor.xml like such:
<object name="Campaign">
<hasOne name="CreatedBy_User">
<relate from="CreatedBy_UserID" to="CBU_UserID" />
</hasOne>
<hasOne name="ModifiedBy_User">
<relate from="ModifiedBy_UserID" to="MBU_UserID" />
</hasOne>
</object>
<object name="User" alias="CreatedBy_User">
<field name="FirstName" alias="CBU_FirstName" />
<field name="LastName" alias="CBU_LastName" />
</object>
<object name="User" alias="ModifiedBy_User">
<field name="FirstName" alias="MBU_FirstName" />
<field name="LastName" alias="MBU_LastName" />
</object>
But that leaves me with a query with 3 of each of the above noted columns.
So, I started down the below path:
<object name="User" alias="CreatedBy_User">
<field name="UserID" alias="CBU_UserID" />
<field name="FirstName" alias="CBU_FirstName" />
<field name="LastName" alias="CBU_LastName" />
<field name="dateCreated" alias="CBU_dateCreated" />
<field name="dateModified" alias="CBU_dateModified" />
<field name="CreatedBy_UserID" alias="CBU_CreatedBy_UserID"/>
<field name="ModifiedBy_UserID" alias="CBU_ModifiedBy_UserID"/>
</object>
<object name="User" alias="ModifiedBy_User">
<field name="UserID" alias="MBU_UserID" />
<field name="FirstName" alias="MBU_FirstName" />
<field name="LastName" alias="MBU_LastName" />
<field name="dateCreated" alias="MBU_dateCreated" />
<field name="dateModified" alias="MBU_dateModified" />
<field name="CreatedBy_UserID" alias="MBU_CreatedBy_UserID"/>
<field name="ModifiedBy_UserID" alias="MBU_ModifiedBy_UserID"/>
</object>
On 4/8/06, Matthew Bryant <[EMAIL PROTECTED]> wrote:
Good morning/evening all,
My first post so be gentle. Loving reactor and am integrating it into
my first app.
THE SITUATION...
I have a Job Table and a User Table.
Their are 2 relationships between the Job and the User
- a job has an owner
- a job has a manager
These are defined by 2 foreign keys in job:
- ownerID
- managerID
THE CURRENT CONFIG....
<object name="Job">
<!-- This section defines the relationship for the Jobs Owner -->
<hasOne name="User">
<relate from="ownerID" to="objectID" />
</hasOne>
<!-- This section defines the relationship for the Jobs
Manager -->
<hasOne name="User">
<relate from="managerID" to="objectID" />
</hasOne>
</object>
<object name="User">
<hasMany name="Job">
<relate from="objectID" to="ownerID" />
</hasOne>
<hasMany name="Job">
<relate from="objectID" to="managerID" />
</hasOne>
</object>
THE REQUEST...
How can I define which relationship I am refering to using the getUserRecord()?
THE SUGGESTION....
I guess the obvious solution would be to provide an Alias on each of
the relationships defined in the xml and then call using something
like:
JobRecord.getUserRecord('Owner') or JobRecord.getUserRecord('Manager')
UserRecord.getJobIterator('Owned') or UserRecord.getJobIterator ('Managed')
<object name="Job">
<hasOne name="User" alias='Owner'>
<relate from="ownerID" to="objectID" />
</hasOne>
<hasOne name="User" alias='Manager'>
<relate from="managerID" to="objectID" />
</hasOne>
</object>
<object name="User">
<hasMany name="Job" alias='Owned'>
<relate from="objectID" to="ownerID" />
</hasOne>
<hasMany name="Job" alias='Managed'>
<relate from="objectID" to="managerID" />
</hasOne>
</object>
REQUEST...
Yes I could restructure and have a 1:many relationship and a flag in
the bridging table that defines whether the relationship is a Manager
or Owner but that is a seperate discussion. Please don't offer as a
solution ;)
FURTHER THOUGHT... ;)
I also have another 1:many relationship from Job to User called
"Developers". But this can wait till the first issue is solved.
-- Matthew Bryant
-- Reactor for ColdFusion Mailing List -- [email protected]
-- Archives at http://www.mail-archive.com/reactor%40doughughes.net/
--
Chris Phillips
www.dealerpeak.com
Senior Application Developer -- Reactor for ColdFusion Mailing List -- [email protected] -- Archives at http://www.mail-archive.com/reactor%40doughughes.net/
