How to get communities of a member

Question

I have in my WebApplication several communities and some members (employees) that joined the community throughout the Webpart "Join Community". I wonder how to get all the communities (just the Title) in which an employee is joined using Object Server Model or other method.

Thank you very much

Answer 1

I found the solution!

I implemented this query that can be used from a Stored procedure in a custom database. This SQL query solved my problem but must be reviewed when a new hotfix be released (The DB Model could change)

SELECT AllWebs.Title, docs.listid, AllWebs.SiteId, 
    AllWebs.FullURL 
FROM AllUserData AS UserData 
LEFT OUTER LOOP JOIN AllUserData AS t2
        ON (UserData.[int1]=t2.[tp_ID]) AND 
            (UserData.[tp_RowOrdinal] = 0) AND 
            (t2.[tp_RowOrdinal] = 0) AND 
            ( (t2.tp_Level = 1) ) AND 
            (t2.[tp_IsCurrentVersion] = CONVERT(bit,1) ) AND 
            (t2.[tp_CalculatedVersion] = 0 ) AND 
            (t2.[tp_DeleteTransactionId] = 0x ) AND 
            (UserData.[tp_IsCurrentVersion] = CONVERT(bit,1) ) AND 
            (UserData.[tp_CalculatedVersion] = 0 ) AND 
            (UserData.[tp_DeleteTransactionId] = 0x ) 
    inner join Docs on UserData.tp_SiteId = Docs.siteId
    inner join AllSites on UserData.tp_SiteId = AllSites.Id
    inner join AllWebs on AllSites.Id = AllWebs.SiteId
WHERE (UserData.tp_Level = 1) AND 
    (UserData.tp_RowOrdinal=0) AND ((UserData.[int2] = 1)) and
    t2.[nvarchar4] = 'username@mail.com' and 
    Docs.LeafName = 'MembersAllItems.aspx' and 
    AllSites.deleted = 0