Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    text
    copied!<h3>Layered Navigation == Faceted Search.</h3> <p>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. </p> <h3>Performance is the main trade-off.</h3> <p>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 (&lt;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.</p> <p>The downside to Solr is that it requires you to configure, <strong>secure</strong> and run yet another service on your server. It can also be pretty CPU and memory intensive depending on your configuration (Tomcat, jetty, etc.).</p> <h3>Faceted search in PHP/MySQL is possible, and not as hard as you'd think.</h3> <p>You need a specific database schema, but it's feasible. Here's a simple example:</p> <p><strong>product</strong></p> <pre><code>+----+------------+ | id | name | +----+------------+ | 1 | blue paint | | 2 | red paint | +----+------------+ </code></pre> <p><strong>classification</strong></p> <pre><code>+----+----------+ | id | name | +----+----------+ | 1 | color | | 2 | material | | 3 | dept | +----+----------+ </code></pre> <p><strong>product_classification</strong></p> <pre><code>+------------+-------------------+-------+ | 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 | +------------+-------------------+-------+ </code></pre> <p>So, say someones search for <code>paint</code>, you'd do something like:</p> <pre><code>SELECT p.* FROM product p WHERE name LIKE '%paint%'; </code></pre> <p>This would return both entries from the <code>product</code> table.</p> <p>Once your search has executed, you can fetch the associated facets (filters) of your result using a query like this one:</p> <pre><code>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; </code></pre> <p>This'll give you something like:</p> <pre><code>+------+----------+-------+ | id | name | value | +------+----------+-------+ | 1 | color | blue | | 1 | color | red | | 2 | material | latex | | 3 | dept | home | | 3 | dept | paint | +------+----------+-------+ </code></pre> <p>So, in your result set, you know that there are products whose color are <code>blue</code> and <code>red</code>, that the only material it's made from is <code>latex</code>, and that it can be found in departments <code>home</code> and <code>paint</code>.</p> <p>Once a user select a facet, just modify the original search query:</p> <pre><code>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; </code></pre> <p>So, here the user is searching for keyword <code>paint</code>, and includes two facets: facet <code>blue</code> for color, and <code>home</code> for department. This'll give you:</p> <pre><code>+----+------------+ | id | name | +----+------------+ | 1 | blue paint | +----+------------+ </code></pre> <p>So, in conclusion. Although it's available out-of-the-box in Solr, it's possible to implement it in SQL fairly easily.</p>
 

Querying!

 
Guidance

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload