問題描述

我計劃在我的電子商務(wù)商店中設(shè)置過濾系統(tǒng)(優(yōu)化您的搜索).您可以在此處查看示例:http://www.bettymills.com/shop/product/find/Air+and+HVAC+Filters

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 等平臺具有所謂的分層導(dǎo)航.

Platforms such as PrestaShop, OpenCart and Magento have what's called a Layered Navigation.

我的問題是，與使用 Solr 或 Lucene 等平臺進行分面導(dǎo)航相比，Magento 或 PrestaShop 等平臺中的分層導(dǎo)航有什么區(qū)別.

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 實現(xiàn)類似的結(jié)果?

Can a similar result be accomplished via just php and mysql?

非常感謝詳細的解釋.

推薦答案

分層導(dǎo)航 == 分面搜索.

它們是同一個東西，但 Magento 和 al 使用不同的措辭，可能是為了吸引眼球.據(jù)我所知，Magento 支持 Solr 分面搜索或 MySQL 搜索.主要區(qū)別在于性能.

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 毫秒)實現(xiàn)快速響應(yīng)時間.雖然 MySQL 進行相同的搜索需要更長的時間，但可以使用索引對其進行優(yōu)化以實現(xiàn)類似的響應(yīng)時間.

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 的缺點是它需要您配置、保護并在您的服務(wù)器上運行另一個服務(wù).根據(jù)您的配置(Tomcat、jetty 等)，它也可能占用大量 CPU 和內(nèi)存.

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.).

您需要一個特定的數(shù)據(jù)庫架構(gòu)，但它是可行的.這是一個簡單的例子:

You need a specific database schema, but it's feasible. Here's a simple example:

產(chǎn)品

+----+------------+
| id | name       |
+----+------------+
|  1 | blue paint |
|  2 | red paint  |
+----+------------+

分類

+----+----------+
| id | name     |
+----+----------+
|  1 | color    |
|  2 | material |
|  3 | dept     |
+----+----------+

product_classification

+------------+-------------------+-------+
| 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  |
+------------+-------------------+-------+

因此，假設(shè)有人搜索 paint，您會執(zhí)行以下操作:

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.

執(zhí)行搜索后，您可以使用如下查詢獲取結(jié)果的相關(guān)方面(過濾器):

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 |
+------+----------+-------+

因此，在您的結(jié)果集中，您知道有些產(chǎn)品的顏色為 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;

所以，這里用戶搜索關(guān)鍵字paint，包括兩個方面:facet 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 中可以相當容易地實現(xiàn)它.

So, in conclusion. Although it's available out-of-the-box in Solr, it's possible to implement it in SQL fairly easily.

這篇關(guān)于分面搜索 (solr) 與通過 PHP 進行的舊過濾?的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網(wǎng)！