-
Re: How i can fetch the user details for "like" and "follow" activities on the contents from Analytics database by DB queries..?
urvashi Dec 23, 2015 3:15 AM (in response to vsoni1)Hi Vivek,
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!
Urvashi
-
Re: How i can fetch the user details for "like" and "follow" activities on the contents from Analytics database by DB queries..?
vsoni1 Dec 23, 2015 3:35 AM (in response to urvashi)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;
-
Re: How i can fetch the user details for "like" and "follow" activities on the contents from Analytics database by DB queries..?
vijayora Dec 23, 2015 4:03 AM (in response to vsoni1)Hi Vivek,
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.
Thanks,
Vijay
-
Re: How i can fetch the user details for "like" and "follow" activities on the contents from Analytics database by DB queries..?
vsoni1 Dec 23, 2015 10:35 PM (in response to vijayora)Hi Vijay Agrawal,
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..?
Thanks,
Vivek
-
-
-
-
Re: How i can fetch the user details for "like" and "follow" activities on the contents from Analytics database by DB queries..?
urvashi Dec 27, 2015 10:29 PM (in response to vsoni1)Hi vivek soni,
Refer these docs. : Is there a query that will tell me the number of likes, comments and views on ALL content?
https://community.jivesoftware.com/docs/DOC-4778#jive_content_id_Outcome_Activity_Codes
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!
Thanks!
Urvashi
-
Re: How i can fetch the user details for "like" and "follow" activities on the contents from Analytics database by DB queries..?
vsoni1 Jan 20, 2016 10:59 PM (in response to urvashi)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
-
Re: How i can fetch the user details for "like" and "follow" activities on the contents from Analytics database by DB queries..?
urvashi Jan 20, 2016 11:24 PM (in response to vsoni1)Hi vivek soni,
Refer this Doc: Clearspace Documentation : Database Schema Guide
Moreover, I guess the table is "jiveGroupUser"
Thanks!
Urvashi
-
-