SQLAlchemy Connection with Subquery Problem

Question

I am trying to translate SQL to 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 get an AttributeError: 'Alias' object has no attribute 'Bill' error AttributeError: 'Alias' object has no attribute 'Bill'

If I configure the SQLAlchemy query to do 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 the error AttributeError: Bill

Any help would be appreciated, thanks!

Answer 1

As soon as you call subquery() , there is no access to objects, but only to columns through .c.{column_name} accessor.

Instead of sub_query do the following: load only the columns you need to avoid name conflicts:

 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()