允许用户搜索项目的最佳方法是什么

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%'

从标题为'%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

三个简短的例子

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.

如果用户搜索苹果,那么我对该产品的标题可能是Golden Delicious Apple。由于复数,行不会返回。

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

我对该产品的标题可能是柠檬蛋糕,但如果用户搜索杯子蛋糕

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.

如果用户搜索苹果和柠檬,那么我应该返回示例1和2中的两行还是应该什么都不返回?什么是最佳实践。

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

仅供参考我使用的是python和peewee。我可以想到如何做到这一点,但这一切都变得非常复杂。

1 个解决方案

#1

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:

SQLite有一个非常好的全文搜索扩展,支持词干(规范化复数形式等)。 Peewee对SQLite FTS有很多支持:

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

Postgresql也有通过tsvector数据类型的全文。 Peewee也支持这个:

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/

关于postgresql搜索的好帖子:http://blog.lostpropertyhq.com/postgres-full-text-search-is-good-enough/

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:

最后,MySQL也支持全文搜索,虽然我还没有使用Peewee进行实验,我很确定它应该是开箱即用的:

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

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

关于问题2,“杯子蛋糕” - >“纸杯蛋糕”,我不确定在这种情况下最好的解决方案是什么。

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.

对于问题3,我知道SQLite将正确处理查询中的布尔表达式,例如“apples AND lemons”将匹配包含两者的文档,而“apples OR lemons”将匹配包含其中一个的文档。我想postgres和mysql也一样。

#1