如何通过关键字匹配数对查询进行排序？

siliconeagle 发表于 Dev

矽鹰

我想Episode通过关键字搜索以下模型。Episode具有将多_key_words集链接到的多对多关系EpisodeKeyword。EpisodeKeyword表示可以搜索的单词。

class Episode(db.Model):
    _key_words = db.relationship('EpisodeKeyword',
        secondary = KeywordEpisode,
        primaryjoin = _id==KeywordEpisode.c.feed_id,
        secondaryjoin = EpisodeKeyword._id==KeywordEpisode.c.keyword_id,
        backref = db.backref('episodes', lazy = 'dynamic'),
        lazy = 'dynamic')


class EpisodeKeyword(db.Model):
    '''
    A keyword for an Episode
    '''
    __table_args__ = {'mysql_engine': 'InnoDB', 'mysql_charset': 'utf8mb4'}

    DB_MAX_WORD_LENGTH = 30
    _id = db.Column(db.Integer, primary_key=True, autoincrement=True)
    _word = db.Column(db.String(DB_MAX_WORD_LENGTH), index = True, unique=True)


KeywordEpisode = db.Table('episode_keywords',
        db.Column('feed_id', db.Integer, db.ForeignKey('episode._id'),index = True),
        db.Column('keyword_id', db.Integer, db.ForeignKey('episode_keyword._id'),index = True)
    )

搜索词被拆分以获取要搜索的关键字列表：

user_input = 'oct 2014'
search_keywords = ['oct', '2014']

我可以使用以下方法获得结果列表：

results = db.session.query(Episode).filter(EpisodeKeyword._word.in_(words)).join(Episode._key_words).join(Episode).all()

但是，我也想按匹配关键字的数量对结果进行排序。例如，如果Episode._key_words同时具有“ oct”和“ 2014”，则它应位于仅匹配一个关键字的项目之前。什么查询将完成此任务？

大卫主义

使用子查询来过滤和计数关键字，然后将其加入最终查询。

# split the query into a list
search = 'oct 2014'
words = search.strip().split()

# use an unnest subquery instead of directly passing the list to in_
# this is for performance reasons for large lists of keywords
words_tab = db.session.query(db.func.unnest(words).label('word')).subquery()

# count the words that match for each episode as a subquery
# this also does the filtering since we're doing the join here
words_q = db.session.query(
    Episode.id,
    db.func.count(Episode.id).label('words')
).join(Episode._key_words
).filter(EpisodeKeyword._word.in_(words_tab)
).group_by(Episode.id
).subquery()

# join on the word query and order by its counts
results = Episode.query.join(
    (words_q, words_q.c.id == Episode.id)
).order_by(words_q.c.words.desc()
).all()