Cfrgtkky

I am trying to find the right way to specify a relationship between SQL alchemy tables that join on conditions from two related tables.

I have a SQL statment equivalent to:

select a.id

     , b.id

     , b.b_val

     , c.id

     , c.c_dt

     , c.c_val

from a

join b

  on a.id = b.id

join c

  on b.id = c.id

  and a.date <-= c.date

  and a.date < current_date;

What I am currently getting out of SQLAlchemy is:

print(q)



SELECT schema.a.id AS schema_a_id

     , schema.b.id AS schema_b_id

     , schema.b.b_val AS schema_b_b_val

     , schema.c.id AS schema_c_id

     , schema.c.c_dt AS schema_c_c_dt

     , schema.c.c_val AS schema_c_c_val 

FROM schema.a 

JOIN schema.b 

  ON schema.a.id = schema.b.id 

JOIN schema.c 

  ON schema.a.secondary_id = schema.c.id

  -- I'm missing the rest of the join clause here

I'm getting thrown off by the join condition to the c table. In it I want to use a constraint on the date column with two conditions. I attempted to do this using the primaryjoin field in my relationship but so far no luck.

Here's my minimum reproducible example:

Base = declarative_base()



class A(Base):

    __tablename__ = 'a'

    __table_args__ = {'schema':'schema'}

    id = Column(Integer(), primary_key=True)

    secondary_id = Column(Integer())

    a_dt = Column(Date())

    b_rel = relationship('b', uselist=False, back_populates="a")

    c_rel = relationship('c', primaryjoin="and_(a.a_dt >= c.c_dt, a.a_dt < func.current_date())")



class B(Base):

    __tablename__ = 'b'

    __table_args__ = {'schema':'schema'}

    id = Column(Integer, ForeignKey(A.id), primary_key=True)

    b_val = Column(Integer())



class C(Base):

    __tablename__ = 'c'

    __table_args__ = {'schema':'schema'}

    id = Column(Integer(), ForeignKey(A.secondary_id), primary_key=True)

    c_dt = Column(Date())

    c_val = Column(Integer())



engine = create_engine('teradatasql://'+user+':'+pswd+'@'+host)



Session = sessionmaker()

Session.configure(bind=engine)

sess = Session()



q = sess.query(A.id, B.id, B.b_val, C.id, C.c_dt, C.c_val).join(B).join(C)



# result is shown above

What am I missing that I could add to cause this relationship to function properly?