CODEWITHSUNDEEP
pythonsqlalchemyflask-sqlalchemy
Shahab
Shahab

Reputation: 694

Python sqlalchemy: group and order by after union of two tuples

I'm trying to group and order a union of two tuples in python using sqlalchemy,

My use-case is:

there are two tables, A & B Both table have two same field share_id and time.

q1 = AModel.query.with_entities(AModel.share_id.label('ID') , AModel.time.label('time')).filter_by(condition)
q2 = BModel.query.with_entities(BModel.share_id.label('ID') , BModel.time.label('time')).filter_by(condition)

result = q1.union(q2).group_by('ID').order_by(desc('time')).all()

I'm trying to get as result date with unique id and max time in relevant table. the order clause only applied to q1 data.

If q2 have duplicate entry with more recent time then The result object didn't represent what want to get from the query

Can some suggest me what to do in my use-case?

Thanks.

Upvotes: 0

Views: 805

Answers (1)

Shahab
Shahab

Reputation: 694

I found a solution to workaround this issue.

q1 = AModel.query.with_entities(AModel.share_id.label('ID') , AModel.time.label('time')).filter_by(condition)
q2 = BModel.query.with_entities(BModel.share_id.label('ID') , BModel.time.label('time')).filter_by(condition)

unionSet = union(q1, q2).alias()
result = db.session.query(unionSet).with_entities(unionSet.c.ID, func.max(unionSet.c.time)) \
            .group_by(unionSet.c.document_id).order_by(func.max(unionSet.c.time).desc()).all()

This will return the expacted result from two table with same columns.

Upvotes: 1

Related Questions