CODEWITHSUNDEEP
pythonmysqlsearch
user4591756
user4591756

Reputation:

What is the best way to allow users to search for items

I have a search feature in my application which allows users to search for products. Currently the query is

select * from products where title like '%search_term%'

This was a quick and hacky way of implementing this. I now want to improve this and wondering how I can do this.

Three short examples

  1. Being able to search for plurals.

My title for the product might be Golden Delicious Apple then if a users searches for apples. Because of the plural the row will not get returned.

  1. When some words could be one/two words

My title for the product might be Lemon Cupcakes but then if a user searches cup cakes

  1. If a user searches apples and lemons then should i return both rows in example 1 and 2 or should I return nothing? What is considered best practice.

FYI I am using python and peewee. I can think of ideas how to do this but it all gets very complicated very fast.

Upvotes: 1

Views: 548

Answers (1)

coleifer
coleifer

Reputation: 26245

Well, depending on what database you are using, you have a couple options.

SQLite has a very good full-text search extension that supports stemming (normalizes plural forms, etc). Peewee has rich support for the SQLite FTS:

Postgresql has full-text as well via the tsvector data type. Peewee also supports this:

Finally, MySQL also supports full-text search, though I have not experimented with it using Peewee I'm pretty sure it should work out of the box:

Regarding question 2, "cup cakes" -> "cupcakes", I'm not sure what the best solution is going to be in that case.

WIth question 3, I know SQLite will correctly handle boolean expressions in queries, e.g. "apples AND lemons" will match documents containing both, whereas "apples OR lemons" will match documents containing one or the other. I imagine postgres and mysql do the same.

Upvotes: 2

Related Questions