3 Replies Latest reply on Jun 12, 2014 12:21 PM by don840

    Activity Type for Friend Requests?

    erinclements


      I've beena sked to look up the number of friend requests that were sent during a certian period of time by a certian group of people.  Being fairly new to these databases, my plan was to use this query, and change the activity type to the activity type for friend request activity type, but I can't find anywhere what that activity type might be.  Does anyone happen to know?

       

      SELECT

      jivedw_activity_fact.activity_ts, jivedw_activity_fact.user_id, jivedw_activity_fact.activity_type, jivedw_object.dw_object_id, jivedw_object.object_type, jivedw_object.object_id, jivedw_object.name, jivedw_container.dw_container_id, jivedw_container.container_type, jivedw_container.container_id, jivedw_container.name, jivedw_user.username, jivedw_user.email, jivedw_user.firstname, jivedw_user.lastname

      FROM

      jivedw_activity_fact, jivedw_user, jivedw_object, jivedw_container

      WHERE jivedw_activity_fact.activity_type = 10 AND jivedw_activity_fact.user_id != -1 AND jivedw_activity_fact.user_id = jivedw_user.user_id AND jivedw_activity_fact.direct_dw_object_id = jivedw_object.dw_object_id  AND jivedw_activity_fact.dw_container_id= jivedw_container.dw_container_id

      AND jivedw_activity_fact.activity_ts between date_trunc('month',current_date) - interval '1 month'

      AND date_trunc('month',current_date) - interval '1 day'

       

      Is tracking the number of friend requests sent even possible?  Thanks so much for any help!!

        • Re: Activity Type for Friend Requests?
          don840

          Hi Erin,

          I was just looking into this yesterday. On our community, the friend request is initiated when a user clicks on 'Add as Friend' button. I was able to see get the Friendship requests sent i.e pending, Rejected, Accepted from the following query in jive application database.

          select count(distinct jiveuserrel.relationshipid)as "Friends Accepted Count"

          , to_char(to_timestamp(jiveuserrel.creationdate/1000), 'YYYY-MM')as "Friendship create date"

          from jiveuserrel

          left join jiveuser on jiveuser.userid = jiveuserrel.relateduserid

          where 1=1

          ---and jiveuser.username like '%erin%'

          and jiveuserrel.state ='approved' --- can use other status like pending, rejected

          group by  to_char(to_timestamp(jiveuserrel.creationdate/1000), 'YYYY-MM')

          order by "Friends Accepted Count" desc