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

i have search feature in application allows users search products. query

select * products title '%search_term%'

this quick , hacky way of implementing this. want improve , wondering how can this.

three short examples

1. being able search plurals.

my title product might golden delicious apple if users searches apples. because of plural row not returned.

2. when words one/two words

my title product might lemon cupcakes if user searches cup cakes

3. if user searches apples , lemons should return both rows in example 1 , 2 or should return nothing? considered best practice.

fyi using python , peewee. can think of ideas how gets complicated fast.

well, depending on database using, have couple options.

sqlite has full-text search extension supports stemming (normalizes plural forms, etc). peewee has rich support sqlite fts:

• http://docs.peewee-orm.com/en/latest/peewee/playhouse.html#ftsmodel
• http://charlesleifer.com/blog/using-sqlite-full-text-search-with-python/

postgresql has full-text via tsvector data type. peewee supports this:

• http://docs.peewee-orm.com/en/latest/peewee/playhouse.html#tsvectorfield
• good post on postgresql search: http://blog.lostpropertyhq.com/postgres-full-text-search-is-good-enough/

finally, mysql supports full-text search, though have not experimented using peewee i'm pretty sure should work out of box:

• https://dev.mysql.com/doc/refman/5.0/en/fulltext-search.html

---

regarding question 2, "cup cakes" -> "cupcakes", i'm not sure best solution going in case.

with question 3, know sqlite correctly handle boolean expressions in queries, e.g. "apples , lemons" match documents containing both, whereas "apples or lemons" match documents containing 1 or other. imagine postgres , mysql same.