使用ginal索引和sqlalchemy返回排名的搜索结果 [英] Returning ranked search results using gin index with sqlalchemy

问题描述

我为全文搜索设置了GIN索引.我想获得一个与搜索查询匹配的记录列表，按排名排序(记录与搜索查询匹配的程度).对于结果，我只需要记录及其列，而不需要用于排序的实际等级值.

I have a GIN index set up for full text search. I would like to get a list of records that match a search query, ordered by rank (how well the record matched the search query). For the result, I only need the record and its columns, I do not need the actual rank value that was used for ordering.

我有以下查询，该查询运行良好，并从我的postgresql数据库返回了预期结果.

I have the following query, which runs fine and returns the expected results from my postgresql db.

SELECT *, ts_rank('{0.1,0.1,0.1,0.1}', users.textsearchable_index_col, to_tsquery('smit:* | ji:*')) AS rank
FROM users
WHERE users.authentication_method != 2 AND users.textsearchable_index_col @@ to_tsquery('smith:* | ji:*') ORDER 
BY rank desc;

我想使用sqlalchemy(SA)执行此查询.我了解到"ts_rank"尚未准备就绪，无法在SA中使用.我尝试了很多事情，例如

I would like to perform this query using sqlalchemy(SA). I understand that 'ts_rank' does not come ready to use in SA. I have tried a number of things, such as

proxy = self.db_session.query(User, text(
                """ts_rank('{0.1,0.1,0.1,0.1}', users.textsearchable_index_col, to_tsquery(:search_str1)) as rank""")). \
                filter(User.authentication_method != 2,
                       text("""users.textsearchable_index_col @@ to_tsquery(:search_str2)""")). \
                params(search_str1=search, search_str2=search). \
                order_by("rank")

并阅读有关使用列属性，尽管我不确定是否/如何在解决方案中使用它.

and also read about using column property, although I'm not sure if/how I would use that in the solution.

将感谢您朝着正确的方向前进.

would appreciate a nudge in the right direction.

推荐答案

您可以通过使用SQLAlchemy

You can use SQL functions in your queries by using SQLAlchemy func

from sqlalchemy.sql.expression import func

(db.session.query(User, func.ts_rank('{0.1,0.1,0.1,0.1}', User.textsearchable_index_col, func.to_tsquery('smit:* | ji:*')).label('rank'))
    .filter(User.authentication_method != 2)
    .filter(User.textsearchable_index_col.op('@@')(func.to_tsquery('smit:* | ji:*')))
    .order_by('rank desc')
).all()

这篇关于使用ginal索引和sqlalchemy返回排名的搜索结果的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！