How i can fetch the user details  for "like" and "follow" activities on the contents from Analytics database by DB queries..?

Hi Urvashi Sharma,

Thanks very much for your response. I am executing following SQL query on Analytics Database. In result of this query i am getting expected data for all the user activities like "Create", "View", "Download", "Delete", "Modify" etc except "Like" and "Follow" activities on any content. I am wondering why the data for only these two activities is not being fetched. Please analyse and let me know if you find any solution for this.

Thanks,

Vivek

QUERY :

SELECT jam.activity_name AS "Action",

    jaw.activity_ts AS "ActivityDate",

    ju.firstname AS "ActorFirstName",

    ju.lastname AS "ActorLastName",

    ju.username AS "ActorUsername",

    jo.name AS "ContentName",

    concat('https://vox.sapient.com/docs/DOC-', jo.object_id) AS "ContentUrl",

    ( SELECT

                CASE

                    WHEN jom.object_name::text = 'Document'::text THEN ( SELECT jodw.name

                       FROM jivedw_object jodw

                      WHERE (jodw.object_id IN ( SELECT jivedw_document.container_id

                               FROM jivedw_document

                              WHERE jivedw_document.document_id = jo.object_id AND jivedw_document.container_type <> 700)) AND jodw.object_type = 600

                     LIMIT 1)

                    ELSE 'SapientNitro Sales Support'::character varying

                END AS "ContentSection") AS "ContentSection",

    jom.object_name AS "ContentObjectType",

    '1329' AS "ContentContainerId",

    'SapientNitro Sales Support' AS "ContentGroupName"

   FROM jivedw_activity_fact jaw,

    jivedw_object_meta jom,

    jivedw_activity_meta jam,

    jivedw_object jo,

    jivedw_user ju

  WHERE (jaw.dw_container_id = 3897 OR (jaw.dw_container_id IN ( SELECT jivedw_container.dw_container_id

           FROM jivedw_container

          WHERE (jivedw_container.container_id IN ( SELECT jivedw_project.project_id

                   FROM jivedw_project

                  WHERE jivedw_project.parent_object_id = 1329)) AND jivedw_container.container_type = 600))) AND jam.activity_type = jaw.activity_type AND ju.user_id = jaw.user_id AND jaw.direct_dw_object_id = jo.dw_object_id AND jom.object_type = jo.object_type AND jaw.activity_ts > '2014-11-30 00:00:00'::timestamp without time zone

  GROUP BY ju.username, ju.firstname, ju.lastname, ju.email, jaw.activity_ts, jom.object_name, jo.name, jaw.direct_object_type, jam.activity_name, jo.object_id

  ORDER BY jaw.activity_ts DESC;

Hi Urvashi Sharma / Vijay Agrawal,

any idea about the table which has mapping between user and groups/spaces...?

basically i am looking for the query which tells me that which user is member of which group. I want to fetch the data of all the users with their groups to which they are member to.

Thanks,

Vivek