python - sqlalchemy查询相关帖子(按常见的多对多关系排序)

Question

在我的 flask 应用程序中，文章和标签之间存在多对多关系:

article_tags =db.Table("article_tags",
    db.Column('article_id', db.Integer, db.ForeignKey('articles.id')),
    db.Column('tag_id', db.Integer, db.ForeignKey('tags.id')))

class Article(db.Model):
    __tablename__ = 'articles'
    id = db.Column(db.Integer, primary_key=True)
    ...
    tags = db.relationship('Tags',secondary=article_tags,backref=db.backref('articles',lazy='dynamic'), lazy='dynamic')

class Tags(db.Model):
    __tablename__="tags"
    id = db.Column(db.Integer,primary_key=True,index=True)
    name = db.Column(db.String(64),unique=True,index=True)

给定一篇特定的文章，我需要查询按共同标签数分组的所有其他文章。例如，来自以下集合:

Article1.tags = tag1,tag2,tag3,tag4
Article2.tags = tag1,tag3,tag5
Article3.tags = tag1,tag3,tag4,tag5

鉴于 Article1，我希望查询返回:

Common Tags | Article
3             Article3 
2             Article2

结果将给出大多数相关帖子的公平近似值。感谢this article 我能够找出一个按标签总数对所有文章进行排序的查询，但我需要仅通过给定文章的公共(public)标签来改进它:

db.session.query(Article,func.count(article_tags.c.tag_id).label('tot
al')).join(article_tags).group_by(Article).order_by('total').all()

如能提供良好查询的任何帮助，我们将不胜感激。

Answer 1

我找到了一个查询，然后将其转换为使用 ORM。
它使用一个子查询。
我使用了 vanilla SQLAlchemy，并以稍微不同的方式创建了我的模型，所以我很想知道您是否遇到任何问题。

article_id = 1

sub_stmt = db.session.query(article_tags.c.tag_id)\
                     .filter(article_tags.c.article_id==article_id)

db.session.query(Article.id,
                 func.count(article_tags.c.tag_id).label('total'),
                 func.group_concat(article_tags.c.tag_id).label('related_tags'))\
          .filter(Article.id!=article_id)\
          .filter(article_tags.c.tag_id.in_(sub_stmt))\
          .filter(article_tags.c.article_id==Article.id)\
          .group_by(Article.id)\
          .order_by(func.count(article_tags.c.tag_id).desc()).all()

结果:

Out[179]: [(3, 3, '1,3,4'), (2, 2, '1,3')]

我在这里写了这个解决方案:https://www.mechanical-meat.com/blog/sqlalchemy-query-related-posts