php - 是否有一个查询来根据字段中以逗号分隔的值的计数来获取排序结果集的结果？
问题描述
嗨，我最近开始研究 Symfony 4。我有 4 个按照 EAV 规则设计的表。eav_entity、eav_attribute、eav_entity_instance、eav_value_text 具有以下字段。
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 |
+----+-------------+--------------+----------------------+---------------------+---------------------+
项目和用户是具有不同属性的两个实体。
在 eav_entity_instance 中，您可以看到用户和员工具有不同的属性 ID。
我的问题是我需要生成查询，以便以没有项目用户参与的方式返回用户的 instance_id。
在 eav_attribute 表中有一个名为 player 的属性，其 id 为 46。
this 的值存储在 eav_value_text 表中，其中 user 的 instance_id 用逗号分隔。
任何人都可以帮我查询，以便每个 instance_id 在玩家逗号分隔值中匹配，并计算每个用户参与的项目数量，结果按计数排序返回。
例如 instance_id 38 是用户的，它在 2 个项目中重复，他们的 instance_id 是 41 和 42。所以类似地，实例 id 为 39 的用户只能在一个值为 42 的值中找到。
因此，由于实例 id 为 38 的用户有 2 个项目，而实例 id 的用户有 1 个项目，如果我们在 ASC 中排序，结果应该如下所示，如果我们在 DESC 中排序，结果应该如下所示。
+---+
|id +
+---+
|38 |
|39 |
+---+
解决方案
在 SQL 服务器中，您可以使用函数 STRING_SPLIT，但快速谷歌搜索显示它在 MySql 中不存在。但是我的搜索结果确实将我指向了这个站点您可以通过以下方式使用这些信息来解决您的问题（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;