'SQLAlchemy: Relationships based on third table
I'm trying to model following situation: I need to set prices for groups of countries. So I have three entities:
class Zone:
zone = Column(Integer, primary_key=True)
class Country:
country = Column(String(2), primary_key=True)
zone = Column(Integer, ForeignKey('Zone.zone')
class Rate:
zone = Column(Integer, ForeignKey('Zone.zone')
rate = Column(Float)
Now I want to be able to access rates for a chosen Country. So I change my Country entity this way:
class Country:
country = Column(String(2), primary_key=True)
zone = Column(Integer, ForeignKey('Zone.zone')
rates = relationship('Rate', foreign_keys=[zone])
And I'm getting an error:
sqlalchemy.exc.NoForeignKeysError: Could not determine join condition between parent/child tables on relationship Country.rates - there are no foreign keys linking these tables. Ensure that referencing columns are associated with a ForeignKey or ForeignKeyConstraint, or specify a 'primaryjoin' expression.
Ok, I changed the Country entity:
class Country:
country = Column(String(2), primary_key=True)
zone = Column(Integer, ForeignKey('Zone.zone')
rates = relationship('Rate', primaryjoin="Country.zone == Rate.zone")
The error is changed:
sqlalchemy.exc.ArgumentError: Could not locate any relevant foreign key columns for primary join condition 'countries.zone = rates.zone' on relationship Country.rates. Ensure that referencing columns are associated with a ForeignKey or ForeignKeyConstraint, or are annotated in the join condition with the foreign() annotation.
So the next version looks like that:
class Country:
country = Column(String(2), primary_key=True)
zone = Column(Integer, ForeignKey('Zone.zone')
rates = relationship('Rate', primaryjoin="foreign(Country.zone) == remote(Rate.zone)")
Surprisingly that partially worked. Partially because value of rates wasn't a list (by the way lazy='dynamic' caused an error as well) but a single (first) value. Somewhere I found a solution to that, which required change rather in Rate:
class Rate:
zone = Column(Integer, ForeignKey('Zone.zone')
rate = Column(Float)
__countries = relationship('Country',
primaryjoin="foreign(Rate.zone) == remote(Country.zone)",
backref='rates')
This finally worked. So here comes a question. The solution seems to be a bit cumbersome and doesn't rely on indexes, which might cause performance issues. Is there a better solution?
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
| Solution | Source |
|---|
