SQLAlchemy按顺序排列多对多的关系 [英] SQLAlchemy ordering by count on a many to many relationship
问题描述
这是我当前模型的简化示例(我正在使用 Flask SQLAlchemy扩展):
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))
$ b $ 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'
)
我试图按照 的顺序订购发布
的数量。
<这是我基本上试图发出的查询:
$ b $ pre $
SELECT p。*,COUNT(l.`pid` )as`likes`
FROM`posts` as p
LEFT JOIN`like` as l
ON p.```` l.`pid`
GROUP BY p。 `id`
ORDER BY`likes` DESC
我只是无法得到任何在SQLAlchemy方面工作的东西。
感谢任何人都可以提供的帮助。
我还没有使用SQLAlchemy,所以我想我会试试看。我没有尝试使用你的模型,我只是写了一些新的(虽然相似):
$ b
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
table,使用 func.count
来计算喜欢的, group_by
Post
然后使用 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文档非常有用。
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'
)
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
I just haven't been able to get anything working on the SQLAlchemy side of things.
Thanks for any help anyone can offer.
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
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')
I found the ORM tutorial and the rest of the SQLAlchemy documentation very useful.
这篇关于SQLAlchemy按顺序排列多对多的关系的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!