Make search use fulltext search

[anlutro]

So that we can sort by match score etc.

[FredG71]

@x89

[anlutro]

http://docs.sqlalchemy.org/en/rel_1_0/dialects/postgresql.html#full-text-search

I can't really make sense of how to translate this to the ORM though.

There's also a package: https://sqlalchemy-searchable.readthedocs.org/en/latest/

[anlutro]

Apparently to sort by relevance with a fulltext search in psql you need to do this, which I have no idea how to do in SQLAlchemy.

SELECT ts_rank_cd(
  to_tsvector('english', table.column),
  to_tsquery('search string')
) AS score

[x89]

Ty for the highlight @rgarnier On 3 Sep 2015 11:00, "Andreas Lutro" notifications@github.com wrote:

Apparently to sort by relevance with a fulltext search in psql you need to do this, which I have no idea how to do in SQLAlchemy.

SELECT ts_rank_cd( to_tsvector('english', table.column), to_tsquery('search string') ) AS score

— Reply to this email directly or view it on GitHub https://github.com/anlutro/qdb/issues/4#issuecomment-137396669.

[anlutro]

@x89 fix

[FredG71]

@x89 what is that @rgarnier

[anlutro]

Got me a working query

select id, body, ts_rank_cd(to_tsvector('english', body), query) as score
from quotes, to_tsquery('linux') query
where query @@ to_tsvector('english', body)
order by score desc;

Doing the to_tsvector twice seems unnecessary, though.

[moopie]

just use microsoft sql server

[x89]

Nice haircut @anlutro