Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
212 views
in Technique[技术] by (71.8m points)

python - SQLAlchemy order by function result

This is the code I have and it is working (returns all problems ordered by difficulty):

def get_noteworthy_problems(self):

    ACategory = aliased(Category)
    AProblem = aliased(Problem)

    all_prob = DBSession.query(AProblem).filter(
        AProblem.parent_id == ACategory.id,
        ACategory.parent_id == self.id)

    noteworthy_problems = 
        sorted(all_prob, key=lambda x: x.difficulty(), reverse=True)

    return noteworthy_problems

But I think I must optimize this code. Is there a possibility to change the code having order_by and my function difficulty()? My function returns a number. I tried something like:

    result = DBSession.query(AProblem).filter(
        AProblem.parent_id == ACategory.id,
        ACategory.parent_id == self.id).order_by(
        AProblem.difficulty().desc())

but I receive the error TypeError: 'NoneType' object is not callable.

See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Reply

0 votes
by (71.8m points)

Hybrid attributes are special methods that act as both a Python property and a SQL expression. As long as your difficulty function can be expressed in SQL, it can be used to filter and order like a normal column.

For example, if you calculate difficulty as the number of parrots a problem has, times ten if the problem is older than 30 days, you would use:

from datetime import datetime, timedelta
from sqlalchemy import Column, Integer, DateTime, case
from sqlalchemy.ext.hybrid import hybrid_property

class Problem(Base):
    parrots = Column(Integer, nullable=False, default=1)
    created = Column(DateTime, nullable=False, default=datetime.utcnow)

    @hybrid_property
    def difficulty(self):
        # this getter is used when accessing the property of an instance
        if self.created <= (datetime.utcnow() - timedelta(30)):
            return self.parrots * 10

        return self.parrots

    @difficulty.expression
    def difficulty(cls):
        # this expression is used when querying the model
        return case(
            [(cls.created <= (datetime.utcnow() - timedelta(30)), cls.parrots * 10)],
            else_=cls.parrots
        )

and query it with:

session.query(Problem).order_by(Problem.difficulty.desc())

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
OGeek|极客中国-欢迎来到极客的世界,一个免费开放的程序员编程交流平台!开放,进步,分享!让技术改变生活,让极客改变未来! Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...