How to create lists with a limit on attachments?

Question

symfony

Hello!
A banal task, but it does not come out to understand how to form one request.

There are two tables:
- groups (id, title) (groups)
- clients (id, username, group_id) (clients)

I want to display the columns of the groups that will have clients, but so that each group has a maximum of 15 people.
The simplest thing I came up with is such a horror:

// Получаю все группы
$groups = $this->getDoctrine()->getRepository(Groups::class)->findAll();
// Создаю массив для финального результата
$clients_lists = [];
foreach ($groups as $group){
  $clients_lists[$group->getId()] = [];
  // Получаю клиентов из этой группы с лимитом 15
  $clients = $this->getDoctrine()->getRepository(Clients::class)->findBy([
    'group_id' => $group->getId()
  ], [], 15);
  // И добавляю их в массив под индексом группы
  $clients_lists[$group->getId()] = $clients;
}

How to do it normally, with a minimum of queries to the database without queries in a cycle?

Answer 1

If you are using MySQL 8.0 or older, you can write the following query:

select *
from (
  select
  clients.*,
  groups.title `group`, 
  row_number() over (partition by clients.id order by clients.id) rn -- order may be different
  from clients
  join `groups` on groups.id = clients.group_id
) client_groups
where rn < 10

MySQL fiddle