我正在尝试将SQL转换为SQLAlchemy。我需要的查询的SQL版本如下:
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
我的SQLAlchemy查询如下:
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()
我收到一个错误AttributeError: 'Alias' object has no attribute 'Bill'
如果我将SQLAlchemy查询调整为以下值:
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))
结果=查询.all()
我得到一个错误AttributeError: Bill
任何帮助都将不胜感激,谢谢!
一旦调用
subquery()
,就无法访问对象,只能通过.c.{column_name}
访问器访问列。请对
sub_query
执行以下操作:仅加载所需的列,以避免任何名称冲突:然后在查询中使用带有
.c.column_name
的列名:相关问题 更多 >
编程相关推荐