Как установить лимит на LEFT JOIN?

Question

[Network2020]

Пытаюсь для каждого пользователя из таблицы users вывести не более 5 фотографий из таблицы users_images. В приведенном ниже коде ограничение не происходит и для каждой строки таблицы users возвращается все записи из users_images, где id совпадает.

Также rn всегда равно единице, т.е. она не увеличивается. Поэтому и не происходит лимит на количество. В чем может быть проблема. Спасибо заранее!

SELECT
    u.`id`,
    u.`name`,
    `ui`.`user_id`,
    `ui`.`name_image`,
     ui.rn

FROM users AS u
    
  LEFT JOIN (SELECT id, user_id, name_image
  @row_num := CASE WHEN @row_num_val = user_id THEN @row_num + 1
                   WHEN (@row_num_val := user_id) IS NOT NULL THEN 1
              END rn
  FROM users_images, (SELECT @row_num := null, @row_num_val := null) AS x
  ORDER BY user_id, id
  ) AS ui ON ui.user_id= u.id and ui.rn <= 5

Comments

[Network2020]

Ребята, никто не знает как решить данную проблему?

[nokimaro]

Network2020, Пробовали сделать двумя отдельными запросами?

[Димон Мартовский]

OUTER APLLY пробовали?

Answer 1

[Vitsliputsli]

Если версия MySQL позволяет, смотрите аналитические функции.
Если нужно на 5.7, то можно применить следующую магию:

select 
        case when @userId<>user_id then @q:=1 else @q:=@q+1 end q,
        case when @q=1 then @userId:=user_id else @userId end user_id
    from (select * users_images order by user_id) t1, (select @userId:=0, @q:=1) t2
    where user_id<>@userId or (user_id=@userId and @q<=2)

Здесь мы получаем из таблицы users_images по 3 элемента в каждой группе по user_id. Лимит установлен в самом конце <=2 - означает 3. Выглядит кошмарно, но решая такие задачи переменными mysql часто получается так.

Comments

[Network2020]

Почему-то это тоже возвращает все значения у которых совпадает users_images.user_id = users.id, без ограничения <=3

[Network2020]

И я так и не понял, сравнение с таблицей users нужно добавить после вашего кода, да?

LEFT JOIN (select 
        case when @userId<>user_id then @q:=1 else @q:=@q+1 end q,
        case when @q=1 then @userId:=user_id else @userId end user_id
    from users_images, (select @userId:=0, @q:=1) t
    where user_id<>@userId or (user_id=@userId and @q<=2)) ui ON ui.user_id = u.id

Так?

[Vitsliputsli]

Network2020, да, именно так. "мы получаем из таблицы users_images по 3 элемента в каждой группе по user_id", полученную таблицу уже объединяйте с тем, что нужно и как нужно.

[Network2020]

Vitsliputsli, а будут ли получены верные данные, если мы изначально выбираем 3 значения, потом их же сравниваем? Т.е. шанс что сравнение с тремя элементами даст результат мизерный, или там именно по условию выбираются эти три элемента? Просто у нас там лимит внутри selecta идет @q<=2

[Vitsliputsli]

Network2020, ни слова не понял.
Приведенный запрос делит таблицу на группы по user_id, и выводит по 3 строки из каждой группы. Просто выполните его и посмотрите на результат. Как уже будете соединять с другими таблицами - это как вам удобнее.
Поправил запрос, забыл сделать предварительную сортировку, без нее не всегда работает. Может из-за этого проблема была, проверьте.

[Network2020]

Vitsliputsli, работает не как нужно, в общем. Ограничение по лимиту вообще не ставит

[Vitsliputsli]

Network2020, у меня это рабоатет так:

create table t34 (user_id int, s varchar(200));

insert into t34 select 1,'q'
union all select 1,'w'
union all select 2,'a'
union all select 2,'s'
union all select 2,'d'
union all select 2,'f'
union all select 2,'g'
union all select 1,'t'
union all select 1,'e'
union all select 1,'r'
;
select 
        case when @userId<>user_id then @q:=1 else @q:=@q+1 end q,
        case when @q=1 then @userId:=user_id else @userId end user_id,
        s
    from (select * from t34 order by user_id) t1, (select @userId:=0, @q:=1) t2
    where user_id<>@userId or (user_id=@userId and @q<=2)

q user_id s
1 1 q
2 1 w
3 1 t
1 2 a
2 2 s
3 2 d