多面搜索(solr)与通过PHP进行良好的旧过滤? [英] Faceted Search (solr) vs Good old filtering via PHP?
问题描述
我计划在我的电子商务商店中建立一个过滤器系统(优化搜索范围).您可以在此处查看示例: http://www.bettymills.com/shop/product /find/Air +和+ HVAC +过滤器
I am planning on setting up a filter system (refine your search) in my ecommerce stores. You can see an example here: http://www.bettymills.com/shop/product/find/Air+and+HVAC+Filters
PrestaShop，OpenCart和Magento等平台具有所谓的分层导航.
Platforms such as PrestaShop, OpenCart and Magento have what's called a Layered Navigation.
我的问题是，与使用Solr或Lucene之类的多面导航相比，Magento或PrestaShop等平台中的分层导航有何区别?
My question is what is the difference between the Layered Navigation in platforms such as Magento or PrestaShop in comparison to using something like Solr or Lucene for faceted navigation.
可以仅通过php和mysql实现类似结果吗?
Can a similar result be accomplished via just php and mysql?
非常感谢详细的解释.
推荐答案
分层导航==多面搜索.
他们是同一回事，但是Magento和al使用了不同的措辞，可能会引起注意.据我所知，Magento支持Solr多面搜索或MySQL搜索.主要区别在于性能.
Layered Navigation == Faceted Search.
They are the same thing, but Magento and al uses different wording, probably to be catchy. As far as I know, Magento supports both the Solr faceted search or the MySQL one. The main difference is the performance.
要在MySQL中进行分面搜索，您需要连接表，而Solr会自动为文档分面编制索引以进行过滤.通常，您可以在平均硬件上使用Solr(对于多方面搜索查询，小于100毫秒)来获得快速的响应时间.虽然MySQL对于相同的搜索将花费更长的时间，但可以通过索引对其进行优化以实现相似的响应时间.
To do faceted search in MySQL requires you to join tables, while Solr indexes the document facets automatically for filtering. You can generally achieve fast response times using Solr (<100ms for a multi-facet search query) on average hardware. While MySQL will take longer for the same search, it can be optimized with indexes to achieve similar response times.
Solr的缺点是它要求您配置，安全并在服务器上运行另一项服务.根据您的配置(Tomcat，码头等)，它也可能占用大量CPU和内存.
The downside to Solr is that it requires you to configure, secure and run yet another service on your server. It can also be pretty CPU and memory intensive depending on your configuration (Tomcat, jetty, etc.).
您需要特定的数据库架构，但这是可行的.这是一个简单的示例:
You need a specific database schema, but it's feasible. Here's a simple example:
产品
+----+------------+
| id | name |
+----+------------+
| 1 | blue paint |
| 2 | red paint |
+----+------------+
分类
+----+----------+
| id | name |
+----+----------+
| 1 | color |
| 2 | material |
| 3 | dept |
+----+----------+
产品分类
+------------+-------------------+-------+
| product_id | classification_id | value |
+------------+-------------------+-------+
| 1 | 1 | blue |
| 1 | 2 | latex |
| 1 | 3 | paint |
| 1 | 3 | home |
| 2 | 1 | red |
| 2 | 2 | latex |
| 2 | 3 | paint |
| 2 | 3 | home |
+------------+-------------------+-------+
因此，假设有人搜索paint，您将执行以下操作:
So, say someones search for paint, you'd do something like:
SELECT p.* FROM product p WHERE name LIKE '%paint%';
这将返回product表中的两个条目.
This would return both entries from the product table.
执行搜索后，您可以使用类似以下查询来获取结果的关联方面(过滤器):
Once your search has executed, you can fetch the associated facets (filters) of your result using a query like this one:
SELECT c.id, c.name, pc.value FROM product p
LEFT JOIN product_classification pc ON pc.product_id = p.id
LEFT JOIN classification c ON c.id = pc.classification_id
WHERE p.name LIKE '%paint%'
GROUP BY c.id, pc.value
ORDER BY c.id;
这会给你类似的东西
+------+----------+-------+
| id | name | value |
+------+----------+-------+
| 1 | color | blue |
| 1 | color | red |
| 2 | material | latex |
| 3 | dept | home |
| 3 | dept | paint |
+------+----------+-------+
因此，在您的结果集中，您知道有些产品的颜色分别为blue和red，唯一制成的材料是latex，并且可以在部门home中找到.和paint.
So, in your result set, you know that there are products whose color are blue and red, that the only material it's made from is latex, and that it can be found in departments home and paint.
一旦用户选择了构面，只需修改原始搜索查询:
Once a user select a facet, just modify the original search query:
SELECT p.* FROM product p
LEFT JOIN product_classification pc ON pc.product_id = p.id
WHERE
p.name LIKE '%paint%' AND (
(pc.classification_id = 1 AND pc.value = 'blue') OR
(pc.classification_id = 3 AND pc.value = 'home')
)
GROUP BY p.id
HAVING COUNT(p.id) = 2;
因此，这里用户正在搜索关键字paint，并且包括两个方面:方面blue用于颜色，以及home用于部门.这会给你:
So, here the user is searching for keyword paint, and includes two facets: facet blue for color, and home for department. This'll give you:
+----+------------+
| id | name |
+----+------------+
| 1 | blue paint |
+----+------------+
因此，总而言之.尽管它可以在Solr中直接使用，但可以很容易地在SQL中实现它.
So, in conclusion. Although it's available out-of-the-box in Solr, it's possible to implement it in SQL fairly easily.
这篇关于多面搜索(solr)与通过PHP进行良好的旧过滤?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
