A given A.id can only be in any one of field1id or field2id in an
instance of B but a B is made up of different A's and hence field1id
and field2id will be different A.id's which cannot appear in any other
object of B.
Let me try to explain with another example, hopefully better this
time.
We have 2 classes: Person and Address. A person can have multiple
addresses (well... let's say 2) but said Person can't have a regular
1:N relationship "addresses" in a list or a set. It needs to have that
information stored in two different 1:1 relationships. Now, having an
instance of an Address, I want to be able to "reach" the Person it
belongs to. One address can only belong to one person.
--------------- Person.py ------------
class Person(declarative_base):
_id = Column("id", Integer, primary_key=True)
_primaryAddressId = Column("primary_address_id", Integer,
ForeignKey("addresses.id"))
_primaryAddress = relationship(
Address.Address, uselist=False,
primaryjoin=
lambda:
(Address.Address.id==Person.primaryAddressId),
back_populate="_person"
)
_secondaryAddressId = Column("secondary_address_id", Integer,
ForeignKey("addresses.id"))
_secondaryAddress = relationship(
Address.Address, uselist=False,
primaryjoin=
lambda:
(Address.Address.id==Person.secondaryAddressId),
back_populate="_person"
)
---------------- Address.py --------------
class Address(declarative_base):
_id = Column("id", Integer, primary_key=True)
_person=relationship("Person",
primaryjoin="or_(Address._id==Person._primaryAddressId,
Address.id==Person._secondaryAddressId)",
uselist=False)"
The following conditions apply -
* A person has a primary and secondary address which no other person
may have.
* A primary address.id != a secondary address.id, which means:
If an "address" is a primary address for person "a", then it can not
be his secondary address. Not for the same person, not for any other
person.
* Only 1 person may live at any address. (There are no families in
this realm), meaning:
One address is going to belong only to one person at an specific time.
* If, for whichever reason, one address is moved from person "John" to
person "Alex", the "_person" relationship of that Address will stop
pointing to "John" and will start pointing to "Alex". In the opposite
manner, "John" will lose that address (will point to None/NULL).
The primaryjoin="or_(...)" shown in the Address class above seems to
be doing the trick, but it has some limitations: If in the future I am
to add a third relationship from Person to Address(let's say a
"vacationAddress") or I want to remove the secondary address, I will
need to go back to the Address.py file and edit the conditions of the
"or_" (in the primary join) accordingly, which is a drone. I am
looking for a smarter "cleaner" way to accomplish this (if there's
any).
On Feb 2, 4:36 pm, Michael Bayer <[email protected]> wrote:
> why have "B.field1d" and separate "B.field2id" then if a given A.id can only
> be in one and only one field1id/field2id ? If you had only one column you
> could at least apply a UNIQUE constraint to it and enforce the 1:1.
>
> On Feb 2, 2012, at 4:31 PM, Manpreet Bhurji wrote:
>
>
>
>
>
>
>
> > Thanks Michael. Really appreciate the quick reply.
>
> > A syntax error in the primary was the reason that back_populate did
> > not work. Fixing that solved it.
>
> > I should have mentioned that field1 and field2 can only have a 1:1
> > relationship towards A. I mean if field1 is assigned to an instance of
> > A, it can't be assigned to any other. So any instance of A will have
> > relationship "b" which only points to 1 B instance.
>
> > The primary join in the A (when I am using back_populate) is now a
> > long string which will need to be edited if I ever change the model B.
> > Any ideas on how I might make this better?
>
> > Regards,
> > Aman
>
> > On Feb 2, 3:36 pm, Michael Bayer <[email protected]> wrote:
> >> On Feb 2, 2012, at 3:16 PM, Manpreet Bhurji wrote:
>
> >>> I have a structure like this
> >>> A.py
> >>> class A:
> >>> _some_fields
>
> >>> B.py
> >>> class B:
> >>> _field1id = Column(...)
> >>> _field1 = relationship( A.A, primaryjoin=lambda:A.A.id==B.field1id,
> >>> backref="b", uselist=False )
> >>> _field2id = Column(...)
> >>> _field2 = relationship( A.A, primaryjoin=lambda:A.A.id==B.field2id,
> >>> backref="b", uselist=False )
>
> >>> I know this will not work because the relationship field1 has already
> >>> created "b" on A.
> >>> I have tried using back_populates="b" in place of backref in class B
> >>> and adding
> >>> _b = relationship( "B", primaryjoin="or_(A.id==B.field1id,
> >>> A.id==B.field2id)" ) to A
>
> >>> Any ideas on how I would go about this?
>
> >> What object would A.b return if its "id" were present both in the
> >> "field1id" of one particular "B", and in the "field2id" of another ?
> >> Would A.b be a collection of both ? If so this requires a union of some
> >> kind, most easily done in Python. Naming the backrefs "b_from_field1"
> >> and "b_from_field2", we'd say:
>
> >> class A(Base):
> >> @property
> >> def b(self):
> >> return self.b_from_field1.union(self.b_from_field2)
>
> >> it's also possible to create a relationship A.b that selects from both
> >> field1id and field2id by creating a UNION and then mapping B to that union
> >> using non_primary=True, then using that mapper as the target of A.b,
> >> though this is a little more involved.
>
> >> In both cases A.b is read-only since it cant be determined if new B()
> >> entries would be established via the B._field1 relationship or the
> >> B._field2 relationship.
>
> > --
> > You received this message because you are subscribed to the Google Groups
> > "sqlalchemy" group.
> > To post to this group, send email to [email protected].
> > To unsubscribe from this group, send email to
> > [email protected].
> > For more options, visit this group
> > athttp://groups.google.com/group/sqlalchemy?hl=en.
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to [email protected].
To unsubscribe from this group, send email to
[email protected].
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
