CODEWITHSUNDEEP
pythonsqlalchemyflask-sqlalchemy
Dof
Dof

Reputation: 29

SQLAlchemy distinct ignoring order_by

I'm trying to run a query from my flask application using SQLAlchemy where I order the results by the timestamp in descending order. From the returned order I want to pull the distinct sender_id's. Unfortunately distinct ignores my requested order_by and pulls the data from the standard table layout. 

messagesReceived = Message.query.filter_by(recipient_id=user_id).order_by(Message.timestamp.desc()).group_by(Message.sender_id).distinct()

I'm still new to SQLAlchemy and through the tutorials and lessons I have done I haven't encountered this, I tried googling but I don't think I am phrasing it correctly to get the answer. I'm currently trying to wrap my head around sub-queries as I think that might be a way to make it work, but asking here in the meantime.

Upvotes: 1

Views: 1544

Answers (2)

Mitalee Rao
Mitalee Rao

Reputation: 191

If you're using PostgreSQL,

messagesReceived = Message.query.filter_by(recipient_id=user_id).order_by(Message.sender_id.asc(), Message.timestamp.desc()).distinct(Message.sender_id).all()

There may not be a need to group by sender_id. Distinct can also be applied at the Query level (affects the entire query, not just the column), as described here, so the order_by needs the sender_id, post which, the distinct can be applied on a specific column.

This may be specific to PostgreSQL, however, so if you're using another DB, I would recommend the distinct expression as outlined here.

from sqlalchemy import distinct, func
stmt = select([func.count(distinct(users_table.c.name))])

Upvotes: 0

Ilja Everilä
Ilja Everilä

Reputation: 52929

Your SQL query is illogical. You select the entire message, but group by person_id. Unless it is unique, it is indeterminate which row the values are selected from for the group row. ORDER BY is logically performed after GROUP BY, and since timestamp is now indeterminate, so is the resulting order. Some SQL DBMS do not even allow such a query to run, as it is not allowed by the SQL standard.

To fetch distinct sender_ids ordered by their latest timestamp per sender_id do

messagesReceived = db.session.query(Message.sender_id).\
    filter_by(recipient_id=user_id).\
    group_by(Message.sender_id).\
    order_by(db.func.max(Message.timestamp))

Upvotes: 1

Related Questions