Returning ranked search results using gin index with sqlalchemy

Question

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.

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;

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.

Answer 1

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()