如何在SQL中实现过滤器系统? [英] How to implement filter system in SQL?

问题描述

目前，我正计划在网站上添加一个过滤器系统.

Right now I am planning to add a filter system to my site.

示例:

(ID=apple, COLOR=red, TASTE=sweet, ORIGIN=US)
(ID=mango, COLOR=yellow, TASTE=sweet, ORIGIN=MEXICO)
(ID=banana, COLOR=yellow, TASTE=bitter-sweet, ORIGIN=US)

所以现在我有兴趣做以下事情: 从此表中选择ID，其中COLOR ='黄色'且TASTE ='SWEET'

so now I am interested in doing the following: SELECT ID FROM thisTable WHERE COLOR='yellow' AND TASTE='SWEET'

但是我的问题是我正在为网站中的多个类别执行此操作，并且各列不一致. (例如，如果该表是用于手机的，那么它将是品牌，3G启用，价格，颜色，波长等)

But my problem is I am doing this for multiple categories in my site, and the columns are NOT consistent. (like if the table is for handphones, then it will be BRAND, 3G-ENABLED, PRICE, COLOR, WAVELENGTH, etc)

我如何设计一个允许这种情况的通用模式?

how could I design a general schema that allows this?

现在我正打算这样做:

table(ID, KEY, VALUE)

这允许任意数量的列，但是对于查询，我正在使用 从表WHERE(KEY = X1 AND VALUE = V1)AND(KEY = X2 AND VALUE = V2)中选择ID，..返回空集.

This allows arbitary number of columns, but for the query, I am using SELECT ID FROM table WHERE (KEY=X1 AND VALUE=V1) AND (KEY=X2 AND VALUE=V2), .. which returns an empty set.

有人可以为此推荐一个好的解决方案吗?请注意，列数将定期更改

Can someone recommend a good solution to this? Note that the number of columns WILL change regularly

推荐答案

实体-您建议的属性值模型可能适用于这种情况.

The entity-attribute-value model that you suggest could fit in this scenario.

关于过滤查询，您必须了解，使用EAV模型会牺牲大量查询能力，因此这可能会变得非常棘手.但是，这是解决您的问题的一种方法:

Regarding the filtering query, you have to understand that with the EAV model you will sacrifice plenty of query power, so this can become quite tricky. However this one way to tackle your problem:

SELECT    stuff.id 
FROM      stuff 
JOIN      (SELECT    COUNT(*) matches
           FROM      table
           WHERE     (`key` = X1 AND `value` = V1) OR 
                     (`key` = X2 AND `value` = V2) 
           GROUP BY  id
          ) sub_t ON (sub_t.matches = 2 AND sub_t.id = stuff.id)
GROUP BY  stuff.id;

此方法的一个重要特征是，您需要指定期望在sub_t.matches = 2中匹配的属性/值对的数量.如果我们有三个条件，则必须指定sub_t.matches = 3，依此类推.

One inelegant feature of this approach is that you need to specify the number of attribute/value pairs that you expect to match in sub_t.matches = 2. If we had three conditions we would have had to specify sub_t.matches = 3, and so on.

让我们建立一个测试用例:

Let's build a test case:

CREATE TABLE stuff (`id` varchar(20), `key` varchar(20), `value` varchar(20));

INSERT INTO stuff VALUES ('apple',  'color',  'red');
INSERT INTO stuff VALUES ('mango',  'color',  'yellow');
INSERT INTO stuff VALUES ('banana', 'color',  'yellow');

INSERT INTO stuff VALUES ('apple',  'taste',  'sweet');
INSERT INTO stuff VALUES ('mango',  'taste',  'sweet');
INSERT INTO stuff VALUES ('banana', 'taste',  'bitter-sweet');

INSERT INTO stuff VALUES ('apple',  'origin',  'US');
INSERT INTO stuff VALUES ('mango',  'origin',  'MEXICO');
INSERT INTO stuff VALUES ('banana', 'origin',  'US');

查询:

SELECT    stuff.id 
FROM      stuff 
JOIN      (SELECT    COUNT(*) matches, id
           FROM      stuff
           WHERE     (`key` = 'color' AND `value` = 'yellow') OR 
                     (`key` = 'taste' AND `value` = 'sweet')
           GROUP BY  id
          ) sub_t ON (sub_t.matches = 2 AND sub_t.id = stuff.id)
GROUP BY  stuff.id;

结果:

+-------+
| id    |
+-------+
| mango |
+-------+
1 row in set (0.02 sec)

现在让我们用color=yellow和taste=sweet插入另一个水果:

Now let's insert another fruit with color=yellow and taste=sweet:

INSERT INTO stuff VALUES ('pear', 'color', 'yellow');
INSERT INTO stuff VALUES ('pear', 'taste', 'sweet');
INSERT INTO stuff VALUES ('pear', 'origin', 'somewhere');

同一查询将返回:

+-------+
| id    |
+-------+
| mango |
| pear  |
+-------+
2 rows in set (0.00 sec)

如果要将此结果限制为具有origin=MEXICO的实体，则必须添加另一个OR条件并检查sub_t.matches = 3而不是2.

If we want to restrict this result to entities with origin=MEXICO, we would have to add another OR condition and check for sub_t.matches = 3 instead of 2.

SELECT    stuff.id 
FROM      stuff 
JOIN      (SELECT    COUNT(*) matches, id
           FROM      stuff
           WHERE     (`key` = 'color' AND `value` = 'yellow') OR 
                     (`key` = 'taste' AND `value` = 'sweet') OR 
                     (`key` = 'origin' AND `value` = 'MEXICO')
           GROUP BY  id
          ) sub_t ON (sub_t.matches = 3 AND sub_t.id = stuff.id)
GROUP BY  stuff.id;

结果:

+-------+
| id    |
+-------+
| mango |
+-------+
1 row in set (0.00 sec)

与每种方法一样，使用EAV模型时也有某些优点和缺点.确保在您的应用程序上下文中广泛研究该主题.您甚至可能要考虑其他关系数据库，例如 Cassandra ， MongoDB ， 伏地魔， SimpleDB 或其他键值存储.

As in every approach, there are certain advantages and disadvantages when using the EAV model. Make sure you research the topic extensively in the context of your application. You may even want to consider an alternative relational databases, such as Cassandra, CouchDB, MongoDB, Voldemort, HBase, SimpleDB or other key-value stores.

这篇关于如何在SQL中实现过滤器系统?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！