SQLAlchemy Query "SELECT AS"

Question

I have the following SQLAlchemy subquery with a join:

ChildModel

id first_name last_name

ParentModel

id first_name last_name

ClassroomModel

id child_id

            subquery = PostgresqlSession().\
            query(ChildModel, ParentModel).\
            outerjoin(ParentModel, ChildModel.last_name == ParentModel.last_name).\
            subquery()

            query = PostgresqlSession().\
                query(subquery, ClassroomModel).\
                join(subquery, subquery.c.student_id == ClassroomModel.student_id)

But I'm getting a AmbiguousColumn error because the subquery has an id column for both ChildModel and ParentModel

What I'd like to do is do a "SELECT AS" for the subquery. I'm looking at the SQLAlchemy documentation about how to do this with select(), so I tried something like:

            subquery = PostgresqlSession().\
            query(ChildModel, ParentModel).\
            select(ChildModel.c.id.label('student_id'), ParentModel.c.id.label('parent_id')).\
            outerjoin(ParentModel, ChildModel.last_name == ParentModel.last_name).\
            subquery()

but select() is not available on the query model. How can I do a SELECT AS on a session query in SQLAlchemy? Thanks!

https://docs.sqlalchemy.org/en/14/tutorial/data.html#selecting-from-labeled-sql-expressions — Gord Thompson, Feb 02 '21 at 20:35
@GordThompson appreciate the link, I had that link several times. As I stated in my issue, I'm using a query object and using .all(), which does not contain a "select()" method. How would I do that using a Session and Query object (again as stated in the question) — ossys, Feb 03 '21 at 04:40

score 1 · Answer 1 · answered Feb 03 '21 at 04:56

Ok I found the answer from this SO post: How can I select only one column using SQLAlchemy?

TL;DR

Indicate your SELECT AS fields in the query() method and use label() function. You must explicitly list ALL attributes if you specify one (can't just specify the id, or it won't return first_name, last_name etc. this is expected)

subquery = PostgresqlSession().\
query(
  ChildModel.id.label('child_id'),
  ChildModel.first_name,
  ChildModel.last_name,

  ParentModel.id.label('parent_id'),
  ChildModel.first_name,
  ChildModel.last_name
).\
outerjoin(ParentModel, ChildModel.last_name == ParentModel.last_name).\
subquery()

SQLAlchemy Query "SELECT AS"

ChildModel

ParentModel

ClassroomModel

1 Answers1