the section at https://docs.sqlalchemy.org/en/latest/orm/join_conditions.html#relationship-to-non-primary-mapper has a recipe for: "when we seek to join from A to B, making use of any number of C, D, etc. in between, however there are also join conditions between A and B directly", I think that's this case because you have conditions between Game/YearStadium directly. So I think use that pattern.
On Thu, Dec 27, 2018 at 6:18 AM <[email protected]> wrote: > > from sqlalchemy import Column, DateTime, String, Integer, ForeignKey, func, > Table, create_engine > from sqlalchemy.orm import relationship, backref, sessionmaker, joinedload, > contains_eager > from sqlalchemy.ext.declarative import declarative_base > > Base = declarative_base() > engine = create_engine('sqlite://', echo=True) > > > class Game(Base): > __tablename__ = 'game' > > id = Column(Integer, primary_key=True) > > stadium_name = Column(String) > month_id = Column(Integer, ForeignKey('month.id')) > > year_stadium = relationship("YearStadium", secondary="month", > primaryjoin="Month.id == Game.month_id", > secondaryjoin="and_(Month.year_name == YearStadium.year_name, " > "YearStadium.stadium_name == Game.stadium_name)") > > > class Month(Base): > __tablename__ = 'month' > > id = Column(Integer, primary_key=True) > > year_name = Column(String) > > > class YearStadium(Base): > __tablename__ = 'year_stadium' > > id = Column(Integer, primary_key=True) > > stadium_name = Column(String) > year_name = Column(String) > > > Base.metadata.drop_all(engine) > Base.metadata.create_all(engine) > Session = sessionmaker(engine) > session = Session() > > > session.query(Game).join(Game.year_stadium).all() # works > session.query(Game).options(joinedload(Game.year_stadium)) # fails > > In the example above, I'm trying to have a relationship to a table. It's not > a classic association table example, since the third table needs one column > from table 2 and one column from table 1 to be joined. > The relationship I wrote works well for simple joins or when lazy-loading, > but fails with a joinedload. > > Is there a way to make it work ? > > -- > SQLAlchemy - > The Python SQL Toolkit and Object Relational Mapper > > http://www.sqlalchemy.org/ > > To post example code, please provide an MCVE: Minimal, Complete, and > Verifiable Example. See http://stackoverflow.com/help/mcve for a full > description. > --- > You received this message because you are subscribed to the Google Groups > "sqlalchemy" group. > To unsubscribe from this group and stop receiving emails from it, send an > email to [email protected]. > To post to this group, send email to [email protected]. > Visit this group at https://groups.google.com/group/sqlalchemy. > For more options, visit https://groups.google.com/d/optout. -- SQLAlchemy - The Python SQL Toolkit and Object Relational Mapper http://www.sqlalchemy.org/ To post example code, please provide an MCVE: Minimal, Complete, and Verifiable Example. See http://stackoverflow.com/help/mcve for a full description. --- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To unsubscribe from this group and stop receiving emails from it, send an email to [email protected]. To post to this group, send email to [email protected]. Visit this group at https://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
