Template SQL function for user exports

Unknown

The query

SELECT
    u.UserID
    ,u.Name
    ,u.Email
    ,IFNULL(u.DateFirstVisit, '') AS DateFirstVisit
    ,IFNULL(u.DateLastActive, '') AS DateLastActive
    ,IFNULL(INET6_NTOA(u.LastIPAddress), u.LastIPAddress) AS LastIPAddress
    ,IFNULL(u.CountVisits, 0) AS CountVisits
    ,IFNULL(u.CountDiscussions, 0) AS CountDiscussions
    ,IFNULL(u.CountComments, 0) AS CountComments
    ,IFNULL(u.Points, 0) AS Points
    /* Roles */
    ,IFNULL(GROUP_CONCAT(DISTINCT r.Name ORDER BY r.Name SEPARATOR ', '), '') AS Roles
    /* Badges */
    ,IFNULL(GROUP_CONCAT(DISTINCT b.Name ORDER BY b.Name SEPARATOR ', '), '') AS Badges
    /* Rank */
    ,IFNULL(ra.Name, '') AS Rank
    /* UserMeta (Profile.*) */
    ,ppum.*
FROM GDN_User AS u
    /* Roles */
    LEFT JOIN GDN_UserRole AS ur USING (UserID)
    LEFT JOIN GDN_Role AS r USING (RoleID)
    /* Badges */
    LEFT JOIN GDN_UserBadge AS ub USING (UserID)
    LEFT JOIN GDN_Badge AS b USING (BadgeID)
    /* Rank */
    LEFT JOIN GDN_Rank AS ra USING (RankID)
    /* UserMeta */
    LEFT JOIN (
        /*
            To get the possible profile fields you can run:
            SELECT REPLACE(Name, 'Profile.', '') FROM GDN_UserMeta WHERE Name LIKE 'Profile.%' GROUP BY Name ORDER BY Name
        */
        SELECT
            UserID
            ,IFNULL(GROUP_CONCAT(IF(Name = 'Profile.Company', NULLIF(Value, ''), NULL)), '') AS Company
            ,IFNULL(GROUP_CONCAT(IF(Name = 'Profile.Country', NULLIF(Value, ''), NULL)), '') AS Country
            ,IFNULL(GROUP_CONCAT(IF(Name = 'Profile.Industry', NULLIF(Value, ''), NULL)), '') AS Industry
            ,IFNULL(GROUP_CONCAT(IF(Name = 'Profile.Jobfunction', NULLIF(Value, ''), NULL)), '') AS Jobfunction
        FROM GDN_UserMeta
        GROUP BY UserID
    ) /*AS PivotProfileUserMeta*/ AS ppum on ppum.UserID = u.UserId
WHERE u.Deleted = 0
    AND u.Admin != 2
GROUP BY u.UserID
;

Strip what is not needed!

Updates

• Add rank

Unknown

Adding ranks to @DaazKu's query.

SELECT
    u.UserID
    ,u.Name
    ,u.Email
    ,IFNULL(u.DateFirstVisit, '') AS DateFirstVisit
    ,IFNULL(u.DateLastActive, '') AS DateLastActive
    ,IFNULL(INET6_NTOA(u.LastIPAddress), u.LastIPAddress) AS LastIPAddress
    ,IFNULL(u.CountVisits, 0) AS CountVisits
    ,IFNULL(u.CountDiscussions, 0) AS CountDiscussions
    ,IFNULL(u.CountComments, 0) AS CountComments
    ,IFNULL(u.Points, 0) AS Points
    /* Roles */
    ,IFNULL(GROUP_CONCAT(DISTINCT r.Name ORDER BY r.Name SEPARATOR ', '), '') AS Roles
    /* Badges */
    ,IFNULL(GROUP_CONCAT(DISTINCT b.Name ORDER BY b.Name SEPARATOR ', '), '') AS Roles
    /* Ranks */
    ,IFNULL(ra.Name, '') AS Rank
    /* UserMeta (Profile.*) */
    ,ppum.*
FROM GDN_User AS u
    /* Roles */
    LEFT JOIN GDN_UserRole AS ur USING (UserID)
    LEFT JOIN GDN_Role AS r USING (RoleID)
    /* Badges */
    LEFT JOIN GDN_UserBadge AS ub USING (UserID)
    LEFT JOIN GDN_Badge AS b USING (BadgeID)
    /* Ranks */
    LEFT JOIN GDN_Rank AS ra USING (RankID)
    /* UserMeta */
    LEFT JOIN (
        /*
            To get the possible profile fields you can run:
            SELECT REPLACE(Name, 'Profile.', '') FROM GDN_UserMeta WHERE Name LIKE 'Profile.%' GROUP BY Name ORDER BY Name
        */
        SELECT
            UserID
            ,IFNULL(GROUP_CONCAT(IF(Name = 'Profile.Company', NULLIF(Value, ''), NULL)), '') AS Company
            ,IFNULL(GROUP_CONCAT(IF(Name = 'Profile.Country', NULLIF(Value, ''), NULL)), '') AS Country
            ,IFNULL(GROUP_CONCAT(IF(Name = 'Profile.Industry', NULLIF(Value, ''), NULL)), '') AS Industry
            ,IFNULL(GROUP_CONCAT(IF(Name = 'Profile.Jobfunction', NULLIF(Value, ''), NULL)), '') AS Jobfunction
        FROM GDN_UserMeta
        GROUP BY UserID
    ) /*AS PivotProfileUserMeta*/ AS ppum on ppum.UserID = u.UserId
WHERE u.Deleted = 0
    AND u.Admin != 2
GROUP BY u.UserID
;

Unknown

TLDR;

The /utility/exportprofiles that CSMs can do gives:

• Name
• Email
• Date of First Visit
• Date of Last Visit
• Discussion Count
• Points
• Invite UserID
• Invited by Name
• Profile Extender Fields

Devs can give this additional info:

• Last IP address
• Visit Count
• Badges
• Rank