How to use order by in flask sqlalchemy for date attribute

Question

I have a flask application and I am using flask sqlalchemy.I have two classes like below:

class User(db.Model, UserMixin):
    id = db.Column(db.Integer, primary_key=True)
    username = db.Column(db.String(20), unique=True, nullable=False)
    email = db.Column(db.String(120), unique=True, nullable=False)
    password = db.Column(db.String(60), nullable=False)
    diaries = db.relationship('Diary', backref='author', lazy=True)


    def __repr__(self):
        return f"User('{self.username}', '{self.email}')"


class Diary(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    diary_date= db.Column(db.DateTime, nullable=False, default=datetime.now())
    emotion = db.Column(db.String(10))
    note = db.Column(db.Text)
    user_id = db.Column(db.Integer, db.ForeignKey('user.id'), nullable=False)

    def __repr__(self):
        return f"Diary('{self.diary_date}', '{self.emotion}','{self.note}')"

I want to order by using diary_date and then take first seven records.I want to show frequent emotion in a week to user.For order by I used following query:

>>> records=(db.session.query(Diary).filter_by(Diary.user_id=User.id).join(Diary
,User.diaries).order_by(Diary.diary_date))

It is giving an error

  File "<stdin>", line 1
SyntaxError: keyword can't be an expression

Is the query wrong?Can you suggest a better query? I am new to sqlalchemy sorry if the question is too dumb.

https://stackoverflow.com/questions/2128505/whats-the-difference-between-filter-and-filter-by-in-sqlalchemy — SuperShoot, Mar 21 '19 at 11:26
change `filter_by(Diary.user_id=User.id)` to `filter(Diary.user_id == User.id)`. — SuperShoot, Mar 21 '19 at 11:27

score 0 · Accepted Answer · answered Mar 21 '19 at 14:37

change filter_by to filter and replace = with ==. It will resolve your error:

db.session.query(Diary).filter(Diary.user_id==User.id).join(Diary,User.diaries).order_by(Diary.diary_date)

And this gives you:

SELECT diary.id AS diary_id, diary.diary_date AS diary_diary_date, diary.emotion AS diary_emotion, diary.note AS diary_note, diary.user_id AS diary_user_id 
FROM user JOIN diary ON user.id = diary.user_id 
WHERE diary.user_id = user.id ORDER BY diary.diary_date

But as you can see WHERE diary.user_id = user.id is unnecessary as it is covered by JOIN expression. It can be safely removed.

Also you can change default=datetime.now() to default=datetime.now. Thanks to that diary_date will be set to time the Diary object is created and not when Diary was parsed by interpreter (all objects would get the same default time).

Thanks it worked.:) But how do you view those records in db itself? — Ruby S, Mar 21 '19 at 15:16
You can just print your query (before calling `.all()`) If you want query with db specific dialect you can look at http://nicolascadou.com/blog/2014/01/printing-actual-sqlalchemy-queries/ — Adrian Krupa, Mar 21 '19 at 15:18

How to use order by in flask sqlalchemy for date attribute

1 Answers1