Secondly, I have a question about joins.
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>
But, I have to tell you that feels wrong.
So, I considered using returnField(). However it doesn't do what "I" thougth it would do.
Are you guys running into these types of situations? If so, what are you all doing about situations like this?
Here is how I would like return field to work when I do a Join. Please let me know what you all think about it working this way.
Currently if I use rreturnField on a query that joins on 3 tables, it will suppress all fields in all tables that I have not specified with a returnField() statement.
But, I would like it to work on each table/object seperately. So, If I said returnField('table2','fieldx') that would supress all the fields other than fieldx from table2, but let all the other fields from table1 through.
But, I would like it to work on each table/object seperately. So, If I said returnField('table2','fieldx') that would supress all the fields other than fieldx from table2, but let all the other fields from table1 through.
Maybe the idea is crazy...
I await your suggestions.
--
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/
