通过SQLAlchemy中的类中包含的方法排序 [英] order by a method contained within a class in SQLAlchemy

问题描述

我目前正在研究一个模型，在该模型中我将判断文章的相关性.这遵循了Hacker News的算法.这是我在app/articles/models.py

I am currently working on a model where I will judge the relevance of an article. This follows Hacker News' algorithm. Here is my articles model in app/articles/models.py

from app.extensions import db

class Article(db.Model):
    """ database representation of an article """
    id = db.Column(db.Integer, primary_key=True)
    title = db.Column(db.String(128))
    subtitle = db.Column(db.String(512))
    body = db.Column(db.Text())
    votes = db.Column(db.Integer, default=1)
    views = db.Column(db.Integer, default=1)
    timestamp = db.Column(db.DateTime, default=datetime.utcnow)

    def popularity(self, gravity=1.8):
        """ uses hacker news popularity rating """
        submit_delta = (self.timestamp - datetime.utcnow()).total_seconds()
        time_decay = submit_delta / 60 / 60
        popularity = (self.views - 1) / (time_decay + 2) ** gravity
        return popularity

当前，我正在尝试按popularity的结果进行排序.

Currently, I am trying to sort by the result of popularity.

>>> from app.articles.models import Article
>>> Article.query.order_by(Article.popularity()).all()

这不起作用.我如何按照文章的受欢迎程度对其进行排序?

this does not work. How would I sort my articles by their popularity?

推荐答案

您可以使用

You can use hybrid methods to create a method that produces a SQL expression when called by the class (for querying) but behaves like a regular method when called by an instance.

这是一个可行的例子.它打印由python和数据库计算出的受欢迎程度.由于时间和舍入的原因，这些会有所不同.

Here's a working example. It prints the popularity as calculated by python and by the database. These will be slightly different due to timing and rounding.

from datetime import datetime
from sqlalchemy import create_engine, Integer, Column, DateTime, func
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.ext.hybrid import hybrid_method
from sqlalchemy.orm import Session

engine = create_engine('postgresql:///example', echo=True)
Base = declarative_base(bind=engine)
session = Session(bind=engine)


class Article(Base):
    __tablename__ = 'article'

    id = Column(Integer, primary_key=True)
    views = Column(Integer, nullable=False, default=1)
    ts = Column(DateTime, nullable=False, default=datetime.utcnow)

    @hybrid_method
    def popularity(self, gravity=1.8):
        seconds = (self.ts - datetime.utcnow()).total_seconds()
        hours = seconds / 3600

        return (self.views - 1) / (hours + 2) ** gravity

    @popularity.expression
    def popularity(self, gravity=1.8):
        seconds = func.extract('epoch', self.ts - func.now())
        hours = seconds / 3600

        return (self.views - 1) / func.power((hours + 2), gravity)


Base.metadata.create_all()

a1 = Article(views=100)
a2 = Article(views=200)

session.add_all((a1, a2))
session.commit()

comparison = session.query(Article, Article.popularity()).all()

for a, pop in comparison:
    print 'py: {} db: {}'.format(a.popularity(), pop)

这适用于PostgreSQL，但是func.power和func.extract在其他数据库中的工作方式可能有所不同. SQLite尤其没有power，并且extract的实现方式有所不同.

This works for PostgreSQL, but func.power and func.extract might work differently in other databases. SQLite especially does not have power and extract is implemented differently.

这篇关于通过SQLAlchemy中的类中包含的方法排序的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！