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

Question

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.

question from:https://stackoverflow.com/questions/65923819/python-sqlalchemy-group-and-order-by-after-union-of-two-tuples

Answer 1

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.