Skip to main content
 首页 » 编程设计

python之这个查询如何按关键字匹配的数量排序

2024年10月25日7grandyang

我想通过关键字搜索以下 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()