Is there a Query to get result as sorted result set based on the count of value that is stored as comma separated in a field?

Question

Hi i have recently started working on Symfony 4. I have 4 tables designed by following EAV rules. eav_entity, eav_attribute, eav_entity_instance, eav_value_text having following fields.


mysql> select * from eav_entity;
+----+---------+---------+---------------------+---------------------+
| id | code    | label   | created_at          | updated_at          |
+----+---------+---------+---------------------+---------------------+
|  5 | user    | User    | 2020-05-07 21:53:48 | 2020-05-07 21:53:48 |
|  6 | project | Project | 2020-05-07 21:53:48 | 2020-05-07 21:53:48 |
+----+---------+---------+---------------------+---------------------+


mysql> select * from eav_attribute;
+----+-----------+-----------------+-----------+-----------+-------------+---------------------+---------------------+
| id | entity_id | code            | type      | is_unique | is_required | created_at          | updated_at          |
+----+-----------+-----------------+-----------+-----------+-------------+---------------------+---------------------+
| 33 |         5 | full_name       | text      |         0 |           1 | 2020-05-07 21:59:20 | 2020-05-07 21:59:20 |
| 34 |         5 | email           | text      |         1 |           1 | 2020-05-07 22:00:32 | 2020-05-07 22:00:32 |
| 35 |         5 | phone_number    | varchar   |         1 |           1 | 2020-05-07 22:00:51 | 2020-05-07 22:00:51 |
| 36 |         5 | password        | varchar   |         1 |           1 | 2020-05-07 22:01:02 | 2020-05-07 22:01:02 |
| 43 |         6 | project_name    | varchar   |         0 |           1 | 2020-05-11 00:49:41 | 2020-05-11 00:49:41 |
| 44 |         6 | project_id      | varchar   |         1 |           1 | 2020-05-11 00:49:41 | 2020-05-11 00:49:41 |
| 45 |         6 | project_manager | varchar   |         0 |           1 | 2020-05-11 00:49:41 | 2020-05-11 00:49:41 |
| 46 |         6 | players         | text      |         0 |           1 | 2020-05-11 00:49:41 | 2020-05-11 00:49:41 |
| 47 |         5 | is_admin        | boolean   |         0 |           0 | 2020-05-12 10:21:33 | 2020-05-12 10:21:33 |
| 49 |         5 | is_enabled      | boolean   |         0 |           0 | 2020-05-16 17:34:03 | 2020-05-16 17:34:03 |
| 51 |         5 | last_activity   | timestamp |         0 |           0 | 2020-05-16 22:20:45 | 2020-05-16 22:20:45 |
| 58 |         5 | experience      | varchar   |         0 |           0 | 2020-05-18 18:26:30 | 2020-05-18 18:26:30 |
| 59 |         5 | branch          | varchar   |         0 |           0 | 2020-05-18 22:18:53 | 2020-05-18 22:18:53 |
+----+-----------+-----------------+-----------+-----------+-------------+---------------------+---------------------+

mysql> select * from eav_entity_instance;
+----+-----------+---------------------+---------------------+
| id | entity_id | created_at          | updated_at          |
+----+-----------+---------------------+---------------------+
| 38 |         5 | 2020-05-16 22:21:50 | 2020-05-16 22:21:50 |
| 39 |         5 | 2020-05-17 21:52:03 | 2020-05-17 21:52:03 |
| 40 |         5 | 2020-05-17 21:53:10 | 2020-05-17 21:53:10 |
| 41 |         6 | 2020-05-17 21:57:24 | 2020-05-17 21:57:24 |
| 42 |         6 | 2020-05-17 22:20:38 | 2020-05-17 22:20:38 |
| 53 |         5 | 2020-05-19 21:47:32 | 2020-05-19 21:47:32 |
| 54 |         5 | 2020-05-19 21:49:07 | 2020-05-19 21:49:07 |
| 55 |         5 | 2020-05-19 21:49:09 | 2020-05-19 21:49:09 |
| 56 |         5 | 2020-05-20 20:57:05 | 2020-05-20 20:57:05 |
+----+-----------+---------------------+---------------------+

mysql> select * from eav_value_text;
+----+-------------+--------------+----------------------+---------------------+---------------------+
| id | instance_id | attribute_id | value                | created_at          | updated_at          |
+----+-------------+--------------+----------------------+---------------------+---------------------+
| 63 |          38 |           33 | Raj                  | 2020-05-16 22:21:50 | 2020-05-16 22:21:50 |
| 64 |          38 |           34 | raj@gmail.com        | 2020-05-16 22:21:50 | 2020-05-16 22:21:50 |
| 65 |          39 |           33 | Ank                  | 2020-05-17 21:52:03 | 2020-05-17 21:52:03 |
| 66 |          39 |           34 | ank@gmail.com        | 2020-05-17 21:52:03 | 2020-05-17 21:52:03 |
| 67 |          40 |           33 | Bas                  | 2020-05-17 21:53:10 | 2020-05-17 21:53:10 |
| 68 |          40 |           34 | bas@gmail.com        | 2020-05-17 21:53:10 | 2020-05-17 21:53:10 |
| 69 |          41 |           46 | 38                   | 2020-05-17 21:57:24 | 2020-05-17 21:57:24 |
| 70 |          42 |           46 | 38,39                | 2020-05-17 22:20:38 | 2020-05-17 22:20:38 |
| 81 |          53 |           34 | vij@gmail.com        | 2020-05-19 21:47:32 | 2020-05-19 21:47:32 |
| 82 |          54 |           34 | abd@gmail.com        | 2020-05-19 21:49:07 | 2020-05-19 21:49:07 |
| 83 |          55 |           34 | jam@gmail.com        | 2020-05-19 21:49:09 | 2020-05-19 21:49:09 |
| 84 |          53 |           33 | Vij                  | 2020-05-19 21:59:35 | 2020-05-19 21:59:35 |
| 85 |          54 |           33 | Abd                  | 2020-05-19 22:04:59 | 2020-05-19 22:04:59 |
| 86 |          56 |           34 | raja@gmail.com       | 2020-05-20 20:57:05 | 2020-05-20 20:57:05 |
| 87 |          55 |           33 | Jam                  | 2020-05-21 16:07:30 | 2020-05-21 16:07:30 |
+----+-------------+--------------+----------------------+---------------------+---------------------+

Project and user are two entities that have different attributes.

In eav_entity_instance you can see that user and employee have different attribute_id.

My problem is i need to generate the query such that instance_id of user is returned in a manner that no of projects user participates in.

In eav_attribute table there is a attribute called players which has id as 46.

The value of this is stored in eav_value_text table where instance_id of user is comma separated.

Can anyone help me with querying such that each instance_id is matched in players comma separated value and count how many project each user is participating in and result is returned in sorted order of count.

For example instance_id 38 is of user and it is repeated in 2 projects whoe's instance_id is 41 and 42. So Similarly user with instance id 39 is found only in one value which is 42.

So since user with instance id 38 has 2 projects and user with instance id has 1 project, result should be like below if we sort it in ASC and reverse if we sort it in DESC.

+---+
|id +
+---+
|38 |
|39 |
+---+

Best suggestion would be DONT store id's in a comma seperated list in a single mysql column. It breaks the normalisation rules. And of course it makes writing a query to make useful use of that list ALMOST impossible — RiggsFolly, May 21 '20 at 16:54
Yeah i agree RiggsFolly, now that i have messed it up i just need to put the fire down. — Rajath S Bangera, May 21 '20 at 16:56
Often the simplest way to get out of this mess is to do quite a bit of the processing in PHP — RiggsFolly, May 21 '20 at 16:58
Sample results are always helpful to people on StackOverflow who would help you. — O. Jones, May 21 '20 at 17:00
Thanks for the suggestiono RiggsFolly and O.Jones, I have updated my question with sample reuslt. — Rajath S Bangera, May 21 '20 at 17:06
https://stackoverflow.com/questions/3653462/is-storing-a-delimited-list-in-a-database-column-really-that-bad — Strawberry, May 21 '20 at 17:28

score 0 · Answer 1 · answered May 21 '20 at 20:38

In SQL server you could use the function STRING_SPLIT but a quick google search revealed that it does not exist in MySql. But my search result did point me to this site You could use the information in the following manner to solve your problem (SQL fiddle):

CREATE TABLE ProjectMembers 
    ( instance_id int
    , value varchar(500)
    );
INSERT INTO ProjectMembers(instance_id, value)
VALUES    (41, '38')
        , (42, '38,39');

/* Be sure that the amount of numbers in this table is at least as long as the 
   maximum amount of project members in a project */
CREATE TABLE Numbers
  (Number int);
INSERT INTO Numbers(Number)
VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9);

SELECT Member, COUNT(*) AS Nr FROM ( 
  SELECT 
    instance_id, 
    substring_index(
      substring_index(value, ',', Number), 
      ','
      , -1
    ) AS Member
  FROM ProjectMembers
  JOIN Numbers
    ON char_length(value) 
      - char_length(replace(value, ',', '')) 
      >= Number - 1
) t
GROUP BY 1
ORDER BY Nr DESC;

Is there a Query to get result as sorted result set based on the count of value that is stored as comma separated in a field?

1 Answers1