python - SQLAlchemy Joining with subquery issue

Question

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!

Answer 1

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