Thanks Chris,
I thought about adding a PK to the table but that still doesn't get
away from the fact that there could be 2 or more rows matching the
personid and positionid. It seems that this table has been used to
keep a history of all the times a person has been in a certain
position. So if a person was in a position from 2005-2006 and then
again in 2007-2009 then there are 2 records in the join table - which
one should reactor update?? It's ambiguous and reactor rightly reports
that.
I think what I need to do is create another join table which uniquely
joins a person to a position and then use the original
tjoinpeopleposition table as a historic record which is added to each
time a save is made.
Sound about right??
Cheers,
Dave
On 23 Apr 2008, at 12:18, Chris Blackwell wrote:
You could do
myRecord =
reactor.createRecord('tjoinpeopleposition').load(PersonId=a,
PositionId=b, StartDate=c, EndDate=d, etc...)
But i'd be more inclined to look at adding a primary key to the table
Chris
On 23/04/2008, David Phipps <[EMAIL PROTECTED]> wrote:
Hi,
Apols for the vague subject line. I am in the process of converting
an Access based system to a Web based, MySQL system. I am using
Reactor to manage the database. There are various tables,join tables
and lookup tables.
When I converted the db from Access to MySQL I assumed (more fool
me) that the join tables were just linking tables. So for the
following tables: tbl_people,tbl_position there is a
tjoinpeopleposition table to join them together as a person can hold
many positions and a position can be linked to many people.
It appears that the tjoinpeopleposition table has been used for more
than just a linking table and holds extra data and just to make it
more tricky there may be multiple rows with the same personid and
positionid (I wanted to use these as the primary key so I could
create a record object). Which means that I can't get a single link
between a Person and a Position.
The rows where the PersonID and PositionID is the same does have
other data which is different (Start Date, Finish Date, some other
ids from other tables).
Is there a neat way in Reactor to get a single record that matches
several criteria. Or do I need to use a custom gateway query to get
the data I need?
For example, if a person is linked to the same position twice, one
record will have a FinishDate and can therefore be ignored.
And if so do I need to do this manually. As in use a gateway query
to get the record based upon the extended criteria and then also
save the data in the same way rather than trying to createRecord and
then save()?
Sorry if this is not clear, my brain is a bit frazzled - you've
gotta love Access!!
Cheers,
Dave
_______________________________________________________________________
David Phipps
Director, Chapel Studios
T +44 (0)20 7100 6980 F +44 (0)20 7100 6981 M +44 (0)7765 240899
New Broad Street House, 35 New Broad Street, London, EC2M 1NH, UK
http://www.chapel-studios.co.uk
_______________________________________________________________________
The Chapel Studios group of companies are registered in England.
'Chapel Studios' and the Chapel Studios logo are registered
trademarks of Chapel Studios. The information in this email is
confidential, intended solely for the addressee, and may be legally
privileged. If you are not the addressee or authorized to receive
this for the addressee, you must not use, copy, disclose or take any
action based upon this message or any information herein. If you
have received this message in error, please advise the sender
immediately by reply e-mail.
-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --
-- -- -- --
Reactor for ColdFusion Mailing List
[EMAIL PROTECTED]
Archives at: http://www.mail-archive.com/reactor%40doughughes.net/
-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --
-- -- -- --
-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --
-- -- -- --
Reactor for ColdFusion Mailing List
[EMAIL PROTECTED]
Archives at: http://www.mail-archive.com/reactor%40doughughes.net/
-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --
-- -- -- --
_______________________________________________________________________
David Phipps
Director, Chapel Studios
T +44 (0)20 7100 6980 F +44 (0)20 7100 6981 M +44 (0)7765 240899
New Broad Street House, 35 New Broad Street, London, EC2M 1NH, UK
http://www.chapel-studios.co.uk
_______________________________________________________________________
The Chapel Studios group of companies are registered in England.
'Chapel Studios' and the Chapel Studios logo are registered trademarks
of Chapel Studios. The information in this email is confidential,
intended solely for the addressee, and may be legally privileged. If
you are not the addressee or authorized to receive this for the
addressee, you must not use, copy, disclose or take any action based
upon this message or any information herein. If you have received
this message in error, please advise the sender immediately by reply e-
mail.
-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --
Reactor for ColdFusion Mailing List
[EMAIL PROTECTED]
Archives at: http://www.mail-archive.com/reactor%40doughughes.net/
-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --
