CODEWITHSUNDEEP
pythonormsqlalchemy
canyon289
canyon289

Reputation: 3515

Creating ORM mappings over subqueries of a table

I'm trying to use SQLAlchemy in a situation where I have a one to many table construct and but I essentially want to create a one to one mapping between tables using a subquery.

For example 

class User:
    __tablename__='user'
    userid = Column(Integer)
    username = Column(String)

class Address:
    __tablename__='address'
    userid = Column(Integer)
    address= Column(String)
    type= Column(String)

In this case the type column of Address includes strings like "Home", "Work" etc. I would like the output to look something like this

enter image description here

I tried using a subquery where I tried

session.query(Address).filter(Address.type =="Home").subquery("HomeAddress")

and then joining against that but then I lose ORM "entity" mapping.

How can I subquery but retain the ORM attributes in the results object?

Upvotes: 0

Views: 252

Answers (1)

univerio
univerio

Reputation: 20548

You don't need to use a subquery. The join condition is not limited to foreign key against primary key:

home_address = aliased(Address, "home_address")
work_address = aliased(Address, "work_address")
session.query(User) \
       .join(home_address, and_(User.userid == home_address.userid,
                                home_address.type == "Home")) \
       .join(work_address, and_(User.userid == work_address.userid,
                                work_address.type == "Work")) \
       .with_entities(User, home_address, work_address)

Upvotes: 1

Related Questions