按多对多关系排序 - SQLAlchemy [英] Sort by Count of Many to Many Relationship - SQLAlchemy
问题描述
我正在尝试使用以下查询来查询标题的建议:
$ b $
res = Title.query.filter(Titles.name.ilike(searchstring))。limit(20)
到目前为止这么好。
现在我想按每个Title对象的订阅者数量来排列结果。
我知道以下问题: SQLAlchemy排序指望一个多对多的关系,但它的解决方案并不适合我。
我收到以下错误:
ProgrammingError:(ProgrammingError)列publishers_1.id必须出现在GROUP BY子句中或用于聚合函数
LINE 1:.. .itles_name,count(titles_users.user_id)AS total,publishers ...
(Publisher有一个我知道答案与子查询有关
下面是我的两个模型的简化示例。
#用户和titl的多对多关系es
titles_users = db.Table('titles_users',
db.Column('user_id',db.Integer,db.ForeignKey('users.id')),
db.Column ('title_id',db.Integer,db.ForeignKey('titles.id'))
)
$ b $ class User(db.Model,UserMixin):
__tablename__ =用户'
#ids
id = db.Column(db.Integer,primary_key = True)
#属性
email = db.Column(db.String(255) = True)
full_name = db.Column(db.String(255))
pull_list = db.relationship(
'Title',
secondary = titles_users,
backref = db.backref('users',lazy ='dynamic'),
lazy ='joined'
)
class Title db.Model):
__tablename__ ='titles'
#:IDs
id = db.Column(db.Integer(),primary_key = True)
#:属性
name = db.Column(db.String(255))
$ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $($)$(db.session.query(Title,func.count(names_users.c .user_id).label(total))
.filter(Title.name.ilike(searchstring))
.outerjoin(names_users).group_by(Title).order_by('total DESC')$
$ b $但是,您的错误包含了一些其他数据,如
publisher
或类似内容。所以如果上面没有帮助,你应该添加更多的信息到你的问题。 I am using Flask-SQLAlchemy to to query my Postgres database.
I am currently trying to query for suggestions of titles with the following query:
res = Title.query.filter(Titles.name.ilike(searchstring)).limit(20)
So far so good.
Now I would like to order the results by the number of "subscribers" each Title object has.
I am aware of the following SO question: SQLAlchemy ordering by count on a many to many relationship however its solution did not work for me.
I am receiving the following error:
ProgrammingError: (ProgrammingError) column "publishers_1.id" must appear in the GROUP BY clause or be used in an aggregate function
LINE 1: ...itles_name, count(titles_users.user_id) AS total, publishers...
(Publisher has a one-to-many relationship with Title models)
I understand that the answer has something to do with subqueries
Below is a simplified example of my two models.
# Many-to-Many relationship for user and titles
titles_users = db.Table('titles_users',
db.Column('user_id', db.Integer, db.ForeignKey('users.id')),
db.Column('title_id', db.Integer, db.ForeignKey('titles.id'))
)
class User(db.Model, UserMixin):
__tablename__ = 'users'
# ids
id = db.Column(db.Integer, primary_key=True)
# Attributes
email = db.Column(db.String(255), unique=True)
full_name = db.Column(db.String(255))
pull_list = db.relationship(
'Title',
secondary=titles_users,
backref=db.backref('users', lazy='dynamic'),
lazy='joined'
)
class Title(db.Model):
__tablename__ = 'titles'
#: IDs
id = db.Column(db.Integer(), primary_key=True)
#: Attributes
name = db.Column(db.String(255))
Code below would work for the model you describe:
q = (db.session.query(Title, func.count(names_users.c.user_id).label("total"))
.filter(Title.name.ilike(searchstring))
.outerjoin(names_users).group_by(Title).order_by('total DESC')
)
for x in q:
print(x)
Your error, however, includes some other data like publisher
or like. So if above is not helpful, you should add more information to your question.
这篇关于按多对多关系排序 - SQLAlchemy的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!