Thanks Dominic, that sounds like a better plan as I will be needing
this table for other modules in a later phase .
2 questions:
If I have the following columns in the tjoinpeopleposition table:
PersonID,
PositionID
,CommitteeMemberTypeID
,ClergyStatusID,StartDate,FinishDate,Year,OtherPosts. Which one(s) do
I set as the Primary Key so that Reactor can load and save a record? I
can't seem to get my head around how reactor will distinguish between
2 (or more) records that are virtually identical apart from a
StartDate or FinishDate.
How do I go about overriding the default load method?
Cheers,
Dave
On 23 Apr 2008, at 13:57, Dominic Watson wrote:
Hi David, I don't think you need a fourth table here. While what you
suggest is logical, the current logic is also correct - a unique
person- position row is defined by the person, the position and the
date they occupied the role. I would suggest adding a primary key
and making sure there is a unique constraint / index on personId,
roleId and dateRange.
If you need to, you can still query a person for unique roles:
SELECT DISTINCT PersonId, RoleId FROM 'tjoinpeopleposition'...
Perhaps this is a situation where you'd want to override the default
load method for the record object and put in custom SQL (rather than
changing the db model to fit in with Reactor).
HTH
Dominic
On 23/04/2008, David Phipps <[EMAIL PROTECTED]> wrote:
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/
-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --
-- -- -- --
--
Blog it up: http://fusion.dominicwatson.co.uk
-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --
-- -- -- --
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/
-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --
