Filtering by nested object fields when lazy='joined' is set in relationship SQLAlchemy 1.4

user_12579033 0
Viewed 49
class ContactType(Base):
    __tablename__ = 'contact_type'
    name = Column(String(255), nullable=False)


class Contact(Base):
    __tablename__ = 'contact'

    first_name = Column(String(255), nullable=False)
    last_name = Column(String(255), nullable=False)   
    contact_type_id = Column(ForeignKey('contact_type.id'), nullable=False)
    contact_type = relationship('ContactType', lazy='joined', innerjoin=True)

ideally, I would filter by Contact.contact_type.name , but it doesn't work that way

query = select(Contact).where(ContactType.name == 'some_type') - doesn't work

query = select(Contact).join(ContactType).where(ContactType.name == 'some_type') - works, but since contact_type = relationship('ContractorType', lazy='joined', innerjoin=True), it already makes a join (in other cases this feature is used) an additional join of the ContactType table is dubbed JOIN (visible when echo= True)

if I use contains_eager

query = select(Contact).options(contains_eager(Contact.contact_type)).where(ContactType.name == 'some_type') - works, but (SAWarning: SELECT statement has a cartesian product between FROM element(s) 'contact_type. Apply join condition(s) between each element to resolve.)

Please tell me how I can do this

sqlalchemy python
1 Answers

I asked the same question in a SQLAlchemy GitHub discussion and they suggested this:

query = (
    select(Contact)
    .join(Contact.contact_type)
    .options(contains_eager(Contact.contact_type))
    .where(ContactType.name == "some_type")
)

I checked it, and it works fine.

user_125790330
Related