Geek asks and answers

Limit results to GROUP_CONCAT () or INNER JOIN

I looked at other topics in detail about limitations on group_concat () and internal connections, but did not find an answer, so I think I will continue and ask him:

I am developing an existing community photo site. I want to get members who have their birthday on that day (today), and then get each of the 5 most highly rated photos. But I also want only the 10 “most beloved” members of the birthday (ie With the highest number of favorites). Here is what I have:

SELECT users.user_id, users.user_name, 
       GROUP_CONCAT(CONVERT(photos.photo_id,char(32)) 
                    ORDER BY photos.average_rate) as photo_ids
FROM users 
INNER JOIN photos ON photos.user_id=users.user_id
WHERE users.day_of_birth = DATE_FORMAT('2012-04-17', '%m-%d') 
  AND users.photo_count>0 
GROUP BY users.user_id
ORDER BY users.favorite_count DESC, users.photo_count DESC LIMIT 0,10

This does what I want, EXCEPT that I cannot limit the number photo_idto 5. This is a problem because the output will be sent as JSON to the application, and some participants have already uploaded over 20,000 photos, which leads to an unacceptably long output line. The only "solution" that seems to work for me is to set the variable group_concat_max_lento something reasonable that will contain at least 5 identifiers, but it is very hacky and unreliable. Is there a way to return exactly 5 photo_idfor a user with a single request? Or do I need to do a loop in my PHP?

I don’t necessarily need photo_id in a comma-separated value, I can also completely exclude the group_concat () method and make an internal join, if possible. But even there I do not know how to limit the results to 5.

+3
2

, MySQL:)

SELECT user_id, user_name, 
    GROUP_CONCAT(CONVERT(photo_id, char(32)) ORDER BY photos.average_rate) as photo_ids
FROM (  SELECT user_id, user_name, photo_id, favorite_count, photo_count, 
            (case when @user_id = user_id then @rownum := @rownum + 1 else CONCAT(@rownum := 1, @user_id := user_id) end) AS dummy_val
        FROM (  SELECT users.user_id, users.user_name, users.favorite_count, users.photo_count, photos.photo_id
                FROM users 
                INNER JOIN photos
                ON photos.user_id=users.user_id
                WHERE users.day_of_birth = DATE_FORMAT('2012-04-17', '%m-%d') 
                    AND users.photo_count > 0 
                ORDER BY users.id ASC, photos.average_rate ASC
             ) AS h, 
             (  @rownum := NULL, 
                @user_id := NULL
             ) AS vars
        HAVING rownum <= 5) AS h2
GROUP BY user_id
ORDER BY favorite_count DESC, photo_count DESC LIMIT 0, 10

6 rownum.

+1
SELECT u.user_id
     , u.user_name 
     , GROUP_CONCAT(p.photo_id ORDER BY p.average_rate) AS photo_ids
FROM 
    ( SELECT user_id
           , user_name
           , favorite_count 
           , photo_count
      FROM users
      WHERE day_of_birth = DATE_FORMAT('2012-04-17', '%m-%d') 
        AND photo_count > 0 
      ORDER BY favorite_count DESC
             , photo_count DESC 
      LIMIT 10
    ) AS u
  INNER JOIN
    photos AS p
      ON  p.user_id = u10.user_id
      AND p.average_rate >= 
          ( SELECT pp.average_rate 
            FROM photos AS pp
            WHERE pp.user_id = u10.user_id
            ORDER BY pp.average_rate DESC
            LIMIT 1 OFFSET 4
          ) 
GROUP BY u.user_id
ORDER BY u.favorite_count DESC
       , u.photo_count DESC
0

More articles:


All Articles