I have the two following tables.
Users Table
id | name | |
---|---|---|
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) )