topic Re: Most Viewed and Liked Documents in alfresco in Alfresco Forum

Most Viewed and Liked Documents in alfresco

sandeepreddy1 — Thu, 22 Mar 2018 09:59:37 GMT

Hi Everyone,By using this query i am getting document creator and created dateSELECT distinct n.audit_creator as creator,p.string_value as document_name,date(n.audit_created) as created_on From alf_node AS n,alf_qname AS q,alf_node_properties as p WHERE n.type_qname_id=q.id AND p.node_id=n.id AND p.

Re: Most Viewed and Liked Documents in alfresco

afaust — Thu, 22 Mar 2018 10:47:09 GMT

First of all, it is really not recommended to access the Alfresco database at a low level. What is wrong with just using the APIs that Alfresco provides for accessing the creator, creation date and name of a document? You can even use Alfresco query features to filter out any documents created by the System or admin users...

There is - by default and without any customisation - no tracking of the "most viewed" documents, so you cannot query for that out-of-the-box. The "like" rating is stored as a property on the document itself, so you can simply to a query for that and sort based on the value. The property in question should be cm:likesRatingSchemeCount.

Re: Most Viewed and Liked Documents in alfresco

sandeepreddy1 — Fri, 23 Mar 2018 10:15:17 GMT

Hi afaust ,

Can you please suggest me, to find API for Most Viewed documents and Liked Documents ..?

Re: Most Viewed and Liked Documents in alfresco

krutik_jayswal — Fri, 23 Mar 2018 10:39:48 GMT

If you are using java , than you can use the nodeService to get the details of property.

if javascript than you can simply, use node.properties["cm:likesRatingSchemeCount"].

Re: Most Viewed and Liked Documents in alfresco

Muffex — Tue, 22 Nov 2022 14:51:21 GMT

Hey,

I know this thread is old and I haven't figured out a suitable solution. However I got "closer". One can count the "ratings" associations. This way there is no filter for the node-type that was rated/liked, but at least this helps to somehow aggregate the amount of ratings/likes a node ("with a name" and "of any kind") got.

SELECT parent_node_id AS nodeDbId, nodeName.string_value AS nodeName, COUNT(parent_node_id) AS likes
FROM public.alf_child_assoc AS assocs
JOIN alf_node AS nodes ON (parent_node_id = nodes.id)
JOIN alf_node_properties nodeName ON (nodes.id = nodeName.node_id AND nodeName.qname_id IN (SELECT id FROM alf_qname WHERE local_name = 'name'))
JOIN alf_qname AS assocType ON (assocType.id = assocs.type_qname_id AND assocType.local_name = 'ratings')
GROUP BY parent_node_id, nodeName.string_value