'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