Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

The default sorting criteria of sqlalchemy?

Now I sort the data in the database by its attribute 1. If there is a tie of different items with same value of attribute 1, the data seems to be sorted by its id. However, I would like to break the tie by sorting by desc(id). How could I change the default sorting criteria of the database if there is a tie?

Thanks!

like image 867
user1909371 Avatar asked Jan 16 '13 14:01

user1909371


2 Answers

Update

Since version 1.1 the order_by parameter in the mapper configuration has been deprecated. Instead Query.order_by must be used.

db.query(User).order_by(User.fullname)

# or in desc order
db.query(User).order_by(User.fullname.desc())

I left here the original answer for historial purposes:

This is possible by means of the mapper configuration.

If you have a user table and want to retrieve the records always ordered by fullname something like this should works:

from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, Integer, String

Base = declarative_base()

class User(Base):
    __tablename__ = 'users'
    id = Column(Integer, primary_key=True)
    name = Column(String)
    fullname = Column(String)
    password = Column(String)

    __mapper_args__ = {
        "order_by": fullname,
    }
    
    def __repr__(self):
       return f"<User(id='{self.id}', name='{self.name}', fullname='{self.fullname}')>"

Default order_by is ascending, if you want to reverse the order this can be used:

__mapper_args__ = {
    "order_by": fullname.desc(),
}
like image 88
Francisco Puga Avatar answered Sep 27 '22 17:09

Francisco Puga


The order is entirely determined by the database, not SQLAlchemy. With plain SQL you just add additional ORDER BY clauses, in SQLAlchemy ORM you do the same by chaining order_by methods. For example:

for eq in session.query(Equipment).order_by(Equipment.model_id).order_by(Equipment.name).all():
    print (eq)

Whichever is left-most is the primary sort.

like image 34
Keith Avatar answered Sep 27 '22 17:09

Keith