sqlalchemy add entity from a subquery

Question

I want to use outerjoin operation on a subquery and also include values from the subquery also.

My code

q_responses = session.query(Candidate, CandidateProfile)
             .join(CandidateProfile, CandidateProfile.candidate_id == Candidate.id)

subq = (session.query(AppAction.candidate_id, Activity.archived)\
        .join(Activity, and_(AppAction.candidate_id == Activity.candidate_id, 
                             Activity.archived == 1)))\
        .subquery("subq")
responses = q_responses.outerjoin(subq, Candidate.id == subq.c.candidate_id).all()

So I get the result in this format

(Candidate, CandidateProfile)

But I also want to include the archived value from subquery in the result.

By reading many relevant posts from the internet, I have tried

add_entity(subq.c.archived)
with_entities
add_column
select_from

But all those have resulted in some error.

Please help me out.

van · Accepted Answer · 2015-09-27 23:56:09Z

3

Please share your error for when you try add_column. The code below should work just fine (assuming that it does work without like which contains add_column):

responses = (
    q_responses
    .add_column(subq.c.archived)  # @new
    .outerjoin(subq, Candidate.id == subq.c.candidate_id)
).all()

Also you could have created a query straight away with this column included:

subq = (
    session.query(AppAction.candidate_id, Activity.archived)
    .join(Activity, and_(AppAction.candidate_id == Activity.candidate_id,
                         Activity.archived == 1))
).subquery("subq")

q_responses = (
    session.query(Candidate, CandidateProfile, subq.c.archived)
    .join(CandidateProfile, CandidateProfile.candidate_id == Candidate.id)
    .outerjoin(subq, Candidate.id == subq.c.candidate_id)
).all()

answered Sep 27, 2015 at 23:56

van

77.6k13 gold badges174 silver badges179 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Hussain Over a year ago

Thanks! I can now get those values from subquery to the outer query.

Collectives™ on Stack Overflow

sqlalchemy add entity from a subquery

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related