I want to fetch the details of users who have performed "Like" and "Follow" activities on any content from Analytics Database by Data base queries. Some one can help please..?
Just by looking into this query we can't get the solution for you. As we need the correct tables for this. Whether this "Like" and "Follow" is getting saved in the table or not?
If yes, then we need to relate them.
And last conclusion!!
Your requirement shows little description
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.
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",
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
WHERE jivedw_document.document_id = jo.object_id AND jivedw_document.container_type <> 700)) AND jodw.object_type = 600
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,
WHERE (jaw.dw_container_id = 3897 OR (jaw.dw_container_id IN ( SELECT jivedw_container.dw_container_id
WHERE (jivedw_container.container_id IN ( SELECT jivedw_project.project_id
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;
One of the reason that i can think of here for not getting Like and Follow is rows is the activity_type returned from jivedw_activity_meta is not matching the records in jivedw_activity_fact .
See what you get when you run following
1st -- select Activity_type from jivedw_activity_meta where Activity_name in ('Like','Follow')
2nd -- select * from jivedw_activity_fact where DIRECT_OBJECT_TYPE in (<Pass the activity type returned from above query>)
-- My guess is 2nd query would not get you anything , thus your join condition "jam.activity_type = jaw.activity_type" should be causing this issue.
I removed the condition "jam.activity_type = jaw.activity_type" from my query and ran it. Now my query is returning the data for all the activities which is there in jivedw_activity_meta table. This data is not correct as users have not performed these activities on the contents. Now i am getting totally incorrect data. Can you please suggest any other solution..?
Refer these docs. : Is there a query that will tell me the number of likes, comments and views on ALL content?
Currently, I don't have access to my database! So i was unable to run the query of yours .
But, As per the data analytic's all the activities whether it is "Like" "Unlike" all fall in one table. So you have to apply the filters.
Try using the POST calls using "Poster" https://api.jivesoftware.com/analytics/v2/export/activity/csv?count=all&after=2015-09-01&before=2015-10-01&filter=action(FOLLOW) -H "Authorization: <yourspecificauthcodehere>" > output.csv
I know you don't want the data in csv format. But give it a try, look out whether you get the "Follow" details or not!
Hope this helps you out!
Hi urvashi / vijayora,
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.
Refer this Doc: Clearspace Documentation : Database Schema Guide
Moreover, I guess the table is "jiveGroupUser"
Retrieving data ...