Skip to content
Advertisement

How to use JOIN and SELECT AS together in SQLAlchemy?

I have the two following tables.

Users Table

id name email
32 Hello e@mail.com
23 World p@mail.com

Sales Table

id SellerId CustomerId Amount
4 32 23 25

I want to join the tables in the following way to get this result. Here I want to only get the entries where customer id is equal to 23.

Id SellerId SellerName SellerEmail Amount
4 32 Hello. e@mail.com 25.

And also do another join this way. Here I want to only get the entries where seller id is equal to 32.

id CustomerId CustomerName CustomerEmail Amount
4 23 World p@mail.com 25.

My code for both the tables is

class Users(Base):
    __tablename__ = "users"

    id = Column(Integer, primary_key=True, index=True)
    name = Column(String)
    email = Column(String, unique=True, index=True, nullable=False)

class Sales(Base):
    __tablename__="sales"

    id = Column(Integer, primary_key=True, index=True)
    seller_id = Column(Integer, index=True)
    customer_id = Column(Integer, index=True)
    amount = Column(Integer, index=True)

I was able to achieve this using raw SQL with the following

SELECT (sales.id), name as SellerName, email as SellerEmail, Amount
FROM sales
LEFT JOIN user ON sales.SellerId = user.id
WHERE CustomerId = 23

Please help with doing the same using SqlAlchemy Session.

Advertisement

Answer

query = (    
    session.query(
        Sales.id,
        Users.name.label('SellerName'),
        Users.email.label('SellerEmail'),
        Sales.amount
    )
    .select_from(Sales)
    .outerjoin(Users, Users.id == Sales.seller_id)
    .filter(Sales.customer_id == 23)
)
4 People found this is helpful
Advertisement