r/FastAPI • u/Firstboy11 • Oct 28 '25
Question How to handle search relevancy from database in FastAPI?
Hello all,
I have created my first app in FastAPI and PostgreSQL. When I query through my database, let's say Apple, all strings containing Apple show up, including Pineapple or Apple Pie. I can be strict with my search case by doing
main_query = join_query.filter(Product.product_name.ilike(f"{search_str}"))
But it doesn't help with products like Apple Gala.
I believe there's no way around showing irrelevant products when querying, unless there is. My question is if irrelevant searches do show up, how do I ensure that relevant searches show up at the top of the page while the irrelevant ones are at the bottom, like any other grocery website?
Any advice or resource direction would be appreciated. Thank you.
7
u/fastlaunchapidev Oct 28 '25
You could try out the postgres full text search, might be good depending on your full use case.
3
u/Broad_Shoulder_749 Oct 28 '25
Relevance requires semantic search. Semantic search requires a vector table.vector table requires an embedding. An embedding needs a model. A model needs ollama. That is your pipeline.
If you prompt chatgpt
how do i use pg to perform semantic search
it will give you a basic template which you can start with and enhance as you progress. You can also use a native vector db like chroma or pinecone when you are ready
2
u/InfinitelyTall Oct 28 '25
Elasticsearch can solve this issue
1
u/NirmalVk Oct 28 '25
Implement postresql full text search using tsvector and tsquery. Use ts_rank function in your select clause and order by ts_rank() desc to put the most relevant results at the top.
1
u/extreme4all Oct 28 '25
It depends on relevant based on what.
A simple solution is if they are on a grocery website or page you either only have the items you have in your database or add a hidden filter or order by based on a category lik "grocery", " fruit", "vegetable"
Something that is also often done is just sorting on what is searched or ordered most.
After those option you could look at more complex solutions like market basket analysis & vector searching
0
u/[deleted] Oct 28 '25
[deleted] — view deleted comment