postgresqljive

Jive 5.0 Current Profile Image


I am currently trying to export profile images from the Jive 5.0 database. I have the postgre query that pulls the images. But I am having difficulties in determining which image is the currently selected image for that profile.

select *
from jiveuser u  
    LEFT JOIN jiveusercontainer c ON u.userid = c.userid  
    LEFT JOIN jivecontent ct ON ct.containerid = c.usercontainerid  
    left join jiveattachment ja on ja.objectid = ct.contentid
WHERE ct.contenttype = 501  and
    ja.objecttype = 501 and
    u.userid in (4794)  and
    filename = 'profile_image_500.png'
order by ja.modificationdate desc

Does anyone know where in the Jive database this information is stored?


Solution

  • This is the query that I believe will pull the current profile image:

    select  ja.attachmentid, c.displayname, ja.creationdate, ja.filename , (select propvalue from jivecontentprop jcpi where jcpi.contentid = ct.contentid and propname = 'index' )
    from jiveuser u  
        LEFT JOIN jiveusercontainer c ON u.userid = c.userid  
        LEFT JOIN jivecontent ct ON ct.containerid = c.usercontainerid  
        left join jiveattachment ja on ja.objectid = ct.contentid    
    WHERE ct.contenttype = 501  and
        ja.objecttype = 501 and
        u.userid in (4794)  and
        filename = 'profile_image_500.png'
    order by propvalue
    limit 1