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.

To a rough approximation, if processing data in PHP (and in the absence of any other aggregating functions), you will never need a GROUP_CONCAT function. Joins will suffice. See https://stackoverflow.com/questions/57359481/get-corrosponding-record-of-row-in-another-column-of-sql-table/57360666#57360666 for something similar — Strawberry, Jan 03 '20 at 13:17
What is your metric for "most effective way"? Fastest? Most compact? Best able to be used in whatever the next task is? Easiest to read? (That example of 17 lines of JSON fails miserably on some of those.) — Rick James, Jan 05 '20 at 23:16
@RickJames I meant the way to use lowest computing resource. I think my source is ineffective, because I'm making a DB call many times. — tonynamy, Jan 07 '20 at 12:15

score 1 · Accepted Answer · answered Jan 07 '20 at 16:49

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

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

1 Answers1