英文:
What is the most effective way to select all data from many-to-many table?
问题
我设计了下面的多对多表格：
activity
----------
id
name
activity_student
----------
id
activity_id
student_id
student
----------
id
name
每个学生可以参加很多活动，每个活动可以有很多参与者。
我想要从活动表中选择数据，同时想要将参与者收集为数组。
我想要生成的最终数据如下：
[
id => 1,
name => 足球俱乐部活动,
participants =>
[
[
id => 1,
name => 示例学生1,
班级 => 3
],
[
id => 3,
name => 示例学生2,
班级 => 5
]
]
]
我尝试了选择 activity.* 并使用 group_concat 获取 student_id。然后我使用 foreach 语句检索了学生的数据。
但我认为这不是最佳做法，因为在 10,000+ 行的情况下，查询时间超过了 10 秒。
最佳做法是什么？
- 我正在使用 CI4，MySQL 数据库，使用 InnoDB 引擎。
英文:
I designed many to many table below:
activity
----------
id
name
activity_student
----------
id
activity_id
student_id
student
----------
id
name
Each students can participate lots of activities, and each activities can have many participants.
And I want to select from activity, and at the same time, I want to collect the participants as array.
The final data I want to make is like:
[
id => 1,
name => football club activity,
participants =>
[
[
id => 1,
name => example student,
class => 3
],
[
id => 3,
name => example student2,
class => 5
]
]
]
I tried to select activity.* and group_concat for student_id. And then I retrieved student's data using foreach statement.
But I think it is not the best practice, since the query time became longer than 10 second with 10,000+ rows.
What is the best practice?
- I am using CI4, mysql database with InnoDB engine.
答案1
得分: 1
以下是已翻译好的内容：
通常情况下，在 SQL 中执行复杂任务比将大量数据传回客户端然后处理数据要更高效，尤其是在你的情况下，你需要不断在数据库和客户端之间进行数据交互。
了解JOIN。
SELECT a.name AS 活动名称,
s.name AS 学生姓名,
...
FROM activity AS a
JOIN activity_student AS 映射 USING(activity_id)
JOIN student AS s USING(student_id)
WHERE ...
你会获得一个表格结构，其中包括活动名称、学生姓名等等。在WHERE中，你可以根据需要筛选出特定的活动或其他条件。
关于高效映射表的建议：http://mysql.rjweb.org/doc.php/index_cookbook_mysql#many_to_many_mapping_table
英文:
It is almost always more efficient to do a complex task in SQL instead of dragging lots of data back to the client and then processing the data. Especially in your case where you would be going back and forth to the database.
Read about JOIN.
SELECT a.name AS ActivityName,
s.name AS StudentName,
...
FROM activity AS a
JOIN activity_student AS map USING(activity_id)
JOIN student AS s USING(student_id)
WHERE ...
You get back a table-like structure that has ActivityName, StudentName, etc. In the WHERE you can filter down to one activity or whatever.
Tips on an efficient schema for the mapping: http://mysql.rjweb.org/doc.php/index_cookbook_mysql#many_to_many_mapping_table
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论