How to set limit on LEFT JOIN?

Question

MySQL

I'm trying to display for each user from the users table no more than 5 photos from the users_images table. In the code below, no constraint occurs and for each row in the users table, all records from users_images where the id matches are returned.

Also, rn is always equal to one, i.e. it does not increase. Therefore, there is no limit on the number. What could be the problem. Thanks in advance!

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

Answer 1

If your MySQL version allows, see analytic functions.
If you need on 5.7, then you can apply the following magic:

select 
        case when @userId<>user_id then @q:=1 else @q:[email protected]+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 ([email protected] and @q<=2)

Here we get from the users_images table 3 elements in each group by user_id. The limit is set at the very end <=2 - means 3. It looks terrible, but when solving such problems with mysql variables, it often turns out like this.