Limit concatenatings in group_concat

Question

I'm just encountering an issue that i have no clue on how to solve. It is related to this (solved) problem.

Like mentioned in the other post, i have a Media table that can hold up many records of the same user, but i only want to display a maximum of six records (in order, In any case, only one Type 1 image, followed by a maximum of five Type 2 images).

The query i now have works fine as long as there is only one Type 1 image, but when i add another Type 1 image, the query displays them both. Unfortunatly, something like ORDER BY UserMedia.m_Type = 1 DESC LIMIT 1 in an GROUP_CONCAT doesn't work, but it is exactly what i need. Anybody a clever idea how to realise this?

I have a SQL Fiddle here with the relevant code. My query looks like this


SELECT
    User.u_UserName, User.u_UserMail, User.u_UserRegistration, 
        Status.us_PaymentStatus, 
        Sex.us_Gender, Sex.us_Interest, 
            Personal.up_Name, Personal.up_Dob, Personal.up_City, Personal.up_Province, 
            UserMedia.m_Id, UserMedia.m_Type, SUBSTRING_INDEX(
                GROUP_CONCAT(
                    CONCAT(
                        UserMedia.m_Type, ':', UserMedia.m_File
                    )
                    ORDER BY UserMedia.m_Type = 1, UserMedia.m_Date DESC SEPARATOR '|'
                ),'|',6
            ) AS userFiles 
FROM User AS User
    JOIN User_Status AS Status ON Status.User_u_UserId = User.u_UserId
    JOIN User_Sex_Info AS Sex ON Sex.User_u_UserId = User.u_UserId
        LEFT JOIN User_Personal_Info AS Personal ON Personal.User_u_UserId = User.u_UserId
        LEFT JOIN Media AS UserMedia ON UserMedia.User_u_UserId = User.u_UserId
WHERE User.u_UserId = '18'
    GROUP BY User.u_UserId

In particular, http://stackoverflow.com/questions/10191815/limit-results-on-an-group-concat-or-inner-join?rq=1 seems helpful — Barmar, Jul 30 '13 at 12:42
Yes i did. I also searched for other, similar, issues but without a satisfying answer. Maybe i didn't look good enough though, i'm still looking. Edit: I overlooked that one. Reading it now — Maurice, Jul 30 '13 at 12:42
I've read it, but i'm afraid that's a bit too much for me. I have no clue on how to apply that solution to my query. I'm not that experienced enough. Can you maybe help me into the right direction? — Maurice, Jul 30 '13 at 13:00
How far do you think you're going to get if you have to get free service from the Internet every time you run into a slightly complicated piece of code? If you don't know what you're doing, you need to hire a real programmer. — Barmar, Jul 30 '13 at 13:05
I'm hoping further each time because when i understand how something works i'm learning, when i hire something i don't have to learn. — Maurice, Jul 30 '13 at 13:13
If you find the answer on your own, you should post it as an answer, not as part of the question. SO is fine with users answering there own questions — StephenTG, Jul 30 '13 at 14:52

score 0 · Accepted Answer · answered Jul 30 '13 at 14:55

Went for a walk and came with the following solution. Maybe not the most beautiful one, but at least it works. I also realised i didn't need the CONCAT function


SELECT
    User.u_UserName, User.u_UserMail, User.u_UserRegistration, 
        Status.us_PaymentStatus, 
        Sex.us_Gender, Sex.us_Interest, 
            Personal.up_Name, Personal.up_Dob, Personal.up_City, Personal.up_Province, 
            UserMedia.m_Id, UserMedia.m_Type, SUBSTRING_INDEX(
                GROUP_CONCAT(
                    UserMedia.m_File
                        ORDER BY UserMedia.m_Type = 1 DESC 
                        SEPARATOR '|'
                ),'|',1
            ) AS userFiles, 
            SUBSTRING_INDEX(
                GROUP_CONCAT(
                    UserMedia.m_File
                        ORDER BY UserMedia.m_Date DESC 
                        SEPARATOR '|'
                ),'|',5
            ) AS userTypes, 
            SUBSTRING_INDEX(
                GROUP_CONCAT(
                    Interests.ui_Interest SEPARATOR '|'
                ),'|',5
            ) AS userInterests
FROM User AS User
    JOIN User_Status AS Status ON Status.User_u_UserId = User.u_UserId
    JOIN User_Sex_Info AS Sex ON Sex.User_u_UserId = User.u_UserId
        LEFT JOIN User_Personal_Info AS Personal ON Personal.User_u_UserId = User.u_UserId
        LEFT JOIN Media AS UserMedia ON UserMedia.User_u_UserId = User.u_UserId
        LEFT JOIN User_Interest_Info AS Interests ON Interests.User_u_UserId = User.u_UserId
WHERE User.u_UserId = :uId
    GROUP BY User.u_UserId

Limit concatenatings in group_concat

1 Answers1