SQLAlchemy 按多对多关系排序

问题描述：

这是我当前模型的一个简化示例(我正在使用 Flask SQLAlchemy 扩展):

This is a simplified example of my current models (I'm using the Flask SQLAlchemy extension):

like = db.Table(
    'like',
    db.Column('uid', db.Integer, db.ForeignKey('users.id')),
    db.Column('pid', db.Integer, db.ForeignKey('posts.id'))
)

class User(db.Model):
    __tablename__ = 'users'

    id = db.Column(db.Integer, primary_key = True)
    username = db.Column(db.String(20))

class Post(db.Model):
    __tablename__ = 'posts'

    id = db.Column(db.Integer, primary_key = True)
    title = db.Column(db.String(255))

    likes = db.relationship(
        'User',
        secondary = like,
        backref = db.backref('likes', lazy = 'dynamic'),
        lazy = 'dynamic'
    )

我正在尝试按赞的数量对 Post 进行排序.

I'm trying to order Post's by the amount of likes it has.

这是我主要尝试发出的查询:

This is the query I'm basically trying to issue:

SELECT p.*, COUNT(l.`pid`) as `likes`
FROM `posts` as p
LEFT JOIN `like` as l
    ON p.`id` = l.`pid`
GROUP BY p.`id`
ORDER BY `likes` DESC

我一直无法在 SQLAlchemy 方面进行任何工作.

I just haven't been able to get anything working on the SQLAlchemy side of things.

感谢任何人提供的任何帮助.

Thanks for any help anyone can offer.

答

我没有经常使用 SQLAlchemy，所以我想我应该试一试.我没有尝试使用你的模型，我只是写了一些新的(虽然足够相似):

I haven't used SQLAlchemy much so I figured I'd give it a shot. I didn't try to use your models, I just wrote some new ones (similar enough though):

likes = db.Table('likes',
    db.Column('user_id', db.Integer, db.ForeignKey('user.id')),
    db.Column('post_id', db.Integer, db.ForeignKey('post.id'))
)

class User(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    username = db.Column(db.String(20))

    def __repr__(self):
        return "<User('%s')>" % self.username

class Post(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    title = db.Column(db.String(255))

    likes = db.relationship('User', secondary = likes,
        backref = db.backref('posts', lazy='dynamic'))

    def __repr__(self):
        return "<Post('%s')>" % self.title

想要加入likes表，使用func.count来统计点赞数，group_by Post和然后使用 order_by:

You want to join the likes table, use func.count to count likes, group_by Post and then use order_by:

db.session.query(Post, func.count(likes.c.user_id).label('total')).join(likes).group_by(Post).order_by('total DESC')

我发现 ORM 教程和其余的 SQLAlchemy 文档非常有用.

I found the ORM tutorial and the rest of the SQLAlchemy documentation very useful.

SQLAlchemy 按多对多关系排序

相关推荐