Search in grouped columns in MySQL?

Question

I need to create a guys database, guys can have one or more attributes, and each guy attribute has a specific meaning, it sounds easy, huh? ok, read on as the problem becomes impossible (5 days with her: s).

So, I create these 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) ); 

with example data:

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

and create this query:

 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; 

which gives me this result:

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

THIS IS A REAL PROBLEM:

Later my boss told me that he wants to filter the data using as many conditions as he wants in order to be able to group these conditions using "ands" and "ors", for example, he might want to make this crazy condition:

Get guys who are older than 10, have less than 18 dollars, have more than 2 candies and less than 10 candies, but regardless of whether the guys are exactly 15, this will translate this filter:

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

I have no problem creating a filter (for this I use jqgrid), the problem is that the attributes are not columns, but rows instead , and because of this I don’t know how to mix the query with the filter, I tried 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 returns: (and I have to get both John and Bob.

SO MUST MIX FILTER AND REQUEST?

Keep in mind that the number of attributes each guy has is the same for all guys, but you can add more attributes and other guys at any time, for example, if I want to add the “Mario” guy, 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); 

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

 -- we insert the attribute apples INSERT INTO attribute VALUES (4, 'apples'); -- we create a value for each guy 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 in apples in my request.

I hope I understand, thanks for all your time :)

Note. Maybe if there was a way to put all the attributes of the guys on the same line ?, 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 | +------+-----------+-------+------+------------+--------+------+------------+--------+------+------------+--------+ 

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 might solve the problem, but performance issues can occur when guys have a lot of attributes (like 30 or more).

Note 3: I cannot change the database schema :(

Answer 1

The following will allow you to make your conditions more or less simple, although I cannot promise that it will be really effective for 100,000 + guys with 30+ attributes. This you must 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 assume the guy has no duplicate attributes.)

Answer 2

how about this?

 SELECT g.name 'guy', a.name 'attribute', v._value 'value' FROM guy g JOIN _value v1 ON g.id = v1.guy_id JOIN attribute a1 ON a1.id = v1.attribute_id JOIN _value v2 ON g.id = v2.guy_id JOIN attribute a2 ON a2.id = v2.attribute_id JOIN _value v3 ON g.id = v3.guy_id JOIN attribute a3 ON a3.id = v3.attribute_id JOIN _value v4 ON g.id = v4.guy_id JOIN attribute a4 ON a4.id = v4.attribute_id JOIN _value v5 ON g.id = v5.guy_id JOIN attribute a5 ON a5.id = v5.attribute_id WHERE ( (a1 = 'age' and v1 > 10) AND (a2 = 'dollars' and v2 < 18) AND (a3 = 'candies' and v3 > 2) AND (a4 = 'candies' and v4 < 10) ) OR (a5 = 'age' and v5 = 15) 

change the fix for a few dumb errors:

 SELECT DISTINCT g.id, g.name 'guy' FROM guy g JOIN _value v1 ON g.id = v1.guy_id JOIN attribute a1 ON a1.id = v1.attribute_id JOIN _value v2 ON g.id = v2.guy_id JOIN attribute a2 ON a2.id = v2.attribute_id JOIN _value v3 ON g.id = v3.guy_id JOIN attribute a3 ON a3.id = v3.attribute_id JOIN _value v4 ON g.id = v4.guy_id JOIN attribute a4 ON a4.id = v4.attribute_id JOIN _value v5 ON g.id = v5.guy_id JOIN attribute a5 ON a5.id = v5.attribute_id WHERE ( (a1.name = 'age' and v1._value > 10) AND (a2.name = 'dollars' and v2._value < 18) AND (a3.name = 'candies' and v3._value > 2) AND (a4.name = 'candies' and v4._value < 10) ) OR (a5.name = 'age' and v5._value = 15) 

in particular, I forgot about the field names in the WHERE , select only the guy fields, and add DISTINCT to get only one row for each guy.

Answer 3

Maybe something like this:

 select g.name as guy from guy g join _value v on g.id = v.guy_id join attribute a on a.id = v.attribute_id where (a.name = 'age' and v._value > 10) or (a.name = 'dollars' and v._value < 18) or (a.name = 'candies' and v._value > 2) group by g.name having count(*) = 3 union select g.name as guy from guy g join _value v on g.id = v.guy_id join attribute a on a.id = v.attribute_id where (a.name = 'age' and v._value = 15) group by g.name -- These two clauses are not necessary, having count(*) = 1 -- they're just her for symmetry 

You turn your external "or" conditions into UNION, and your "and" conditions can be handled by the usual " having count(*) matches the number of conditions."

I don’t know if this approach will work for everything your boss wants, but maybe it will help.

Answer 4

If the problem is “the problem is that the attributes are not columns, but rows instead,” what about the view. You cannot change the database schema, but you can consider a view that:

 CREATE VIEW the_attributes as select a.id, a.name as attribute_name, v._value from attribute a JOIN value v ON v.attribute_id = a.id 

Starting this may work better.

Then I think you should do this:

 select guy.id from guy JOIN the_attributes ON the_attributes.guy_id = guy.id where the_attributes.name = 'age' and _value > 10 and the_attributes.name = 'dollar' and _value < 18 and the_attributes.name = 'candies' and _value > 2 and the_attributes.name = 'candies' and _value <10 ) or the_attributes.name = 'age' and _value = 15 ) 

Whether all this will ultimately help you judge, but this is what arose because of me when I first read the problem. Of course, it looks readable; (

Answer 5

try this, maybe this will help.

 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 WHERE a.ID = v.attribute_ID AND v._value = 'values you want' AND NOT v._value = 'values you don''t want' 

let me know if you need anything else.