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