Вывод 10 последних альбомов по 5 изображений в каждом или как сделать GROUP_CONCAT + LIMIT?

Question

[Sekira]

Есть таблица альбомов (album_id, name) и таблица изображений (image_id, album_id, name), нужно вывести 10 последних альбомов и по 5 последних изображений в каждом.

То есть нужен запрос, что-то наподобие этого:

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

Но limit 5 не учитывается.

Также встречались решения с локальными переменными и join, но там выборка по всей таблице, без лимита альбомов, а только лимит изображений.

Еще встречался вариант, просто обрезать строку до 5 запятой, но это плохой вариант, особенно если будет много изображений в альбоме.

Заранее всем спасибо!

Answer 1

[egor_nullptr]

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;

Comments

[Sekira]

Спасибо, если не найдется варианта без функции, придется делать так.

Answer 2

[Immortal_pony]

Самый простой вариант - использовать 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

Можно вытаскивать необходимые данные по одному экземпляру

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

НЕ РАБОТАЕТ. Также можно группировать с объединением после получения необходимой выборки в подзапросе:

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

Comments

[Sekira]

Про первый вариант я писал в описании, что он плохой, так как все равно выборка происходит по всем записям, просто потом отрезается.

Второй вариант я пробовал, он не работает, так как поле `albums`.`album_id` недоступно при двойном вложении запроса.

[Immortal_pony]

И действительно. И с переменными тоже не сработает.
Тогда остаются следующие варианты:

1. Написать процедуру.
2. Сделать более одного sql-запроса для получения необходимых данных.
3. Использовать не очень красиво выглядящие конструкции:

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