Reputation: 3400
I am trying to translate SQL into SQLAlchemy. The SQL version of the query I want is as follows:
SELECT * from calendarEventAttendee
JOIN calendarEventAttendanceActual ON calendarEventAttendanceActual.id = calendarEventAttendee.attendanceActualId
LEFT JOIN
(SELECT bill.id, bill.personId, billToEvent.eventId FROM bill JOIN billToEvent ON bill.id = billToEvent.billId) b
ON b.eventId = calendarEventAttendee.eventId AND b.personId = calendarEventAttendee.personId
WHERE b.id is NULL
My SQLAlchemy query is as follows:
query = db.session.query(CalendarEventAttendee).join(CalendarEventAttendanceActual)
sub_query = db.session.query(Bill, BillToEvent).join(BillToEvent, BillToEvent.billId == Bill.id).subquery()
query = query.outerjoin(sub_query, and_(sub_query.Bill.personId == CalendarEventAttendee.personId, Bill.eventId == CalendarEventAttendee.eventId))
results = query.all()
I am getting an error AttributeError: 'Alias' object has no attribute 'Bill'
If I adjust the SQLAlchemy query to the following:
sub_query = db.session.query(Bill, BillToEvent).join(BillToEvent, BillToEvent.billId == Bill.id).subquery()
query = query.outerjoin(sub_query, and_(sub_query.Bill.personId == CalendarEventAttendee.personId, sub_query.BillToEvent.eventId == CalendarEventAttendee.eventId))
results = query.all()
I get an error AttributeError: Bill
Any help would be appreciated, thanks!
Upvotes: 36
Views: 53104
Reputation: 77092
Once you call subquery()
, there is no access to objects, but only to columns via .c.{column_name}
accessor.
Do the following for sub_query
instead: load only the columns you need in order to avoid any name collisions:
sub_query = db.session.query(
Bill.id, Bill.personId, BillToEvent.eventId
).join(BillToEvent, BillToEvent.billId == Bill.id).subquery()
Then in your query use column names with .c.column_name
:
query = query.outerjoin(
sub_query, and_(
sub_query.c.personId == CalendarEventAttendee.personId,
sub_query.c.eventId == CalendarEventAttendee.eventId)
)
results = query.all()
Upvotes: 72