Output 10 latest albums with 5 images each or how to do GROUP_CONCAT + LIMIT?

Question

PHP

There is an album table (album_id, name) and an image table (image_id, album_id, name), you need to display the 10 latest albums and 5 latest images in each.
That is, you need a request, something like this:

select `name`, (select GROUP_CONCAT(`images`.`name`) from `images` where `images`.`album_id`=`albums`.`album_id` order by `images`.`image_id` desc limit 5) from `albums` order by `album_id` desc limit 10

But limit 5 is not taken into account.
There were also solutions with local variables and join, but there is a selection across the entire table, without an album limit, but only an image limit.
There was also an option, just cut the line to 5 commas, but this is a bad option, especially if there are a lot of images in the album.
Thank you all in advance!

Answer 1

delimiter $$
drop function if exists `last_images_names`$$
create function `last_images_names` (aid int, ilimit int)
    returns varchar(255)
    reads sql data
begin
    return (select group_concat(`name`) from (
        select `name` from `images` where `album_id` = aid
        order by `image_id` desc limit ilimit
    ) as _i);
end$$    
delimiter ;

select `name`, last_images_names(`id`, 5) from `albums` order by `album_id` desc limit 10;

Answer 2

The easiest option is to use SUBSTRING_INDEX:

SELECT 
  `name`, 
    (SELECT 
      SUBSTRING_INDEX(
        GROUP_CONCAT(`images`.`name` SEPARATOR ","),
        ",", 
        5
      ) 
    FROM `images` 
    WHERE `images`.`album_id`=`albums`.`album_id` 
    ORDER BY `images`.`image_id` DESC
    ) 
FROM `albums` 
ORDER BY `album_id` DESC 
LIMIT 10

You can pull out the necessary data one by one

SELECT 
  `albums`.`name`,
  CONCAT_WS(',',
    (SELECT `images`.`name` 
    FROM `images` 
    WHERE `images`.`album_id`=albums.`album_id`
    ORDER BY `images`.`image_id` DESC 
    LIMIT 0,1
    ),
    
    (SELECT `images`.`name` 
    FROM `images` 
    WHERE `images`.`album_id`=albums.`album_id`
    ORDER BY `images`.`image_id` 
    DESC LIMIT 1,1
    ),
    
    (SELECT `images`.`name` 
    FROM `images` 
    WHERE `images`.`album_id`=albums.`album_id`
    ORDER BY `images`.`image_id` 
    DESC LIMIT 2,1
    ),
    
    (SELECT `images`.`name` 
    FROM `images` 
    WHERE `images`.`album_id`=albums.`album_id`
    ORDER BY `images`.`image_id` 
    DESC LIMIT 3,1
    ),
    
    (SELECT `images`.`name` 
    FROM `images` 
    WHERE `images`.`album_id`=albums.`album_id`
    ORDER BY `images`.`image_id` 
    DESC LIMIT 4,1
    )		
  ) AS 'images'
FROM `albums`
ORDER BY `album_id` DESC 
LIMIT 10

DOES NOT WORK. It is also possible to group with a union after obtaining the necessary selection in a subquery:

SELECT 
  `name`, 
    (SELECT 
      GROUP_CONCAT(innerquery.`name`)
    FROM		
      (SELECT `images`.`name`
      FROM `images` 
      WHERE `images`.`album_id`=`albums`.`album_id` 
      ORDER BY `images`.`image_id` DESC
      LIMIT 5
      ) AS innerquery
    ) 
FROM `albums` 
ORDER BY `album_id` DESC 
LIMIT 10