在MySQL中搜索分组的列? [英] Search in grouped columns in MySQL?

问题描述

我需要创建一个男生数据库，男生可以具有一个或多个属性，每个男生的属性都有特定的值，听起来容易吗?好吧，请继续阅读，因为该问题几乎是不可能的(处理5天:s).

I need to create a database of guys, guys can have one or more attributes, and each guy's attribute has a specific value, sounds easy eh? well, keep reading as the problem kinda gets impossible (5 days dealing with it :s).

所以我创建了这3个表:

So I create this 3 tables:

CREATE TABLE guy (
  id int(11),
  name varchar(255)
);

CREATE TABLE attribute (
  id int(11),
  name varchar(255)
);

-- each value references one guy and one attribute
CREATE TABLE _value (
  id int(11),
  guy_id int(11),
  attribute_id int(11),
  _value varchar(255)
);

包含以下示例数据:

INSERT INTO attribute VALUES (1, 'age'), (2, 'dollars'), (3, 'candies');
INSERT INTO guy VALUES (1, 'John'), (2, 'Bob');
INSERT INTO _value VALUES (1, 1, 1, 12), (2, 1, 2, 15), (3, 1, 3, 3);
INSERT INTO _value VALUES (4, 2, 1, 15), (5, 2, 2, 20), (6, 2, 3, 6);

并创建此查询:

SELECT g.name 'guy', a.name 'attribute', v._value 'value' 
FROM guy g 
JOIN _value v ON g.id = v.guy_id 
JOIN attribute a ON a.id = v.attribute_id;

这给了我这个结果:

+------+-----------+-------+
| guy  | attribute | value |
+------+-----------+-------+
| John | age       | 12    |
| John | dollars   | 15    |
| John | candies   | 3     |
| Bob  | age       | 15    |
| Bob  | dollars   | 20    |
| Bob  | candies   | 6     |
+------+-----------+-------+

这是真正的问题:

后来，我的老板告诉我他想使用尽可能多的条件来过滤数据，例如，他希望能够使用"ands"和"or"对这些条件进行分组，例如，他可能想执行以下疯狂条件:

Later on, my boss tells me he wants to filter data using as many conditions as he wants an be able to groups those conditions with "ands" and "ors", for example, he may want to do this crazy condition:

获得年龄大于10岁，少于18美元，拥有2个以上糖果和少于10个糖果的人，但是无论如何，还包括年龄恰好为15岁的人. 这将转换为此过滤器:

Get guys which age is greater than 10, have less than 18 dollars, have more than 2 candies and less than 10 candies, but no matter what, also include guys which age is exactly 15. This would translate to this filter:

-- should return both John and Bob
(age > 10 and dollars < 18 and candies > 2 and candies < 10) or (age = 15)

创建过滤器没有问题(为此我使用jqgrid)，问题是属性不是列，而是行，，因此我不知道如何将查询与过滤器混合，我尝试使用以下方法:

I have no problem creating the filter (I use jqgrid for that), the problem is that attributes aren't columns, but rows instead, and because of that I don't know how to mix the query with the filter, I tried with something like this:

SELECT g.name 'guy', a.name 'attribute', v._value 'value' 
FROM guy g 
JOIN _value v ON g.id = v.guy_id 
JOIN attribute a ON a.id = v.attribute_id
GROUP BY guy
HAVING (
    (attribute = 'age' and value > 10) AND
    (attribute = 'dollars' and value < 18) AND
    (attribute = 'candies' and value > 2) AND
    (attribute = 'candies' and value < 10)
       )
OR
       (
     (attribute = 'age' and value = 15)
       )

但只返回鲍勃:(我应该同时得到约翰和鲍勃.

but only Bob is returned :( and I should get both John and Bob.

那么，我应该如何混合过滤器和查询?

请记住，每个人具有的属性数对于所有人都是相同的，但是可以随时添加更多属性和更多人，例如，如果我要添加人"Mario"，我会这样做:

Have in mind that the number of attributes each guy has is the same for all guys, but more attributes and more guys can be added anytime, for example, if I want to add the guy 'Mario' I would do:

-- we insert the guy Mario
INSERT INTO guy VALUES (3, 'Mario');
-- with age = 5, dollars = 100 and candies = 1
INSERT INTO _value VALUES (7, 3, 1, 5), (8, 3, 2, 100), (9, 3, 3, 1);

如果我想创建'apples'属性，我会这样做:

And if I want to create the attribute 'apples' I would do:

-- we insert the attribute apples
INSERT INTO attribute VALUES (4, 'apples');
-- we create a value for each guy's new attribute, John as 7 apples, Bob has 3 and Mario has 8
INSERT INTO _value VALUES (10, 1, 4, 7), (11, 2, 4, 2), (12, 3, 4, 8);

现在，我应该可以在查询中包括有关苹果的条件.

and now I should be able to include conditions about apples in my query.

我希望我能理解自己，谢谢您的所有时间:)

I hope I made myself understandable, thank you for all your time :)

注意:也许如果有一种方法可以将每个人的所有属性都放在一行中?，类似这样:

Note: Maybe If there were a way to put all of each guys attributes in one row?, something like this:

+------+-----------+-------+------+------------+--------+------+------------+--------+------+------------+--------+
| guy  | attribute | value | guy  | attribute  | value  | guy  | attribute  | value  | guy  | attribute  | value  |
+------+-----------+-------+------+------------+--------+------+------------+--------+------+------------+--------+
| John | age       |    12 | John | dollars    |     15 | John | candies    |      3 | John | apples     |      7 |
| Bob  | age       |    15 | Bob  | dollars    |     20 | Bob  | candies    |      6 | Bob  | apples     |      2 |
| Mario| age       |    5  | Mario| dollars    |     100| Mario| candies    |      1 | Mario| apples     |      8 |
+------+-----------+-------+------+------------+--------+------+------------+--------+------+------------+--------+

注2:@iim建议(在此问题中:

Note 2: @iim suggested (In this question: How to search in grouped columns in MySQL? (also in Hibernate if possible)) that I could do a self-join for each attribute, and yes that may solve the problem, but there may be performance problems when guys have tons of attributes (like 30 or more).

注3:我无法更改数据库架构:(

Note 3: I cannot change the database schema :(

推荐答案

以下内容将使您或多或少地简化您的条件，尽管我不能保证对于拥有100,000多个具有30多个属性的人来说，它确实是有效的.你应该自己看看.

The following will let you make your conditions more or less straightforward, though I can't promise it will be really efficient with 100,000+ guys with 30+ attributes. That you should see for yourself.

SELECT g.name guy, a.name attribute, v._value value
FROM guy g 
JOIN _value v ON g.id = v.guy_id 
JOIN attribute a ON a.id = v.attribute_id
GROUP BY guy
HAVING (
    SUM(a.name = 'age'     and v._value > 10) = 1 AND
    SUM(a.name = 'dollars' and v._value < 18) = 1 AND
    SUM(a.name = 'candies' and v._value > 2 ) = 1 AND
    SUM(a.name = 'candies' and v._value < 10) = 1
       )
OR
       (
    SUM(a.name = 'age'     and v._value = 15) = 1
       )

(我在这里假设一个人不能有重复的属性.)

(I'm assuming here that a guy cannot have duplicate attributes.)

这篇关于在MySQL中搜索分组的列?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！