What is the most effective way to select all data from many-to-many table?

Question

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.

Answer 1

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