Portal Engine Questions on portal engine and web parts.
Version 6.x > Portal Engine > Repeater - Sort Results by Popularity View modes: 
User avatar
Member
Member
Bev - 8/2/2012 3:08:25 AM
   
Repeater - Sort Results by Popularity
Morning all,

Am hoping someone might have an easy solution to something that has me a bit stumped!!

I have a repeater set-up which displays items that are of the same document type, but rather than sorting than in alphabetical order or by a field within the document type I want to sort them by popularity. Where popularity is based on the number of hits that particular item has had. Is there an easy way to grab the number of hits an item has had, and then use it to ORDER BY??

I have the feeling that this will probably require some coding, but thought I would ask in case there is an easy way of doing it.

Thanks in advance for any info,

Bev

User avatar
Kentico Developer
Kentico Developer
kentico_helenag - 8/2/2012 5:25:19 AM
   
RE:Repeater - Sort Results by Popularity
Hello,

Do you mean the popularity = number of page views in web analytics? Or clicks to for example a button or link?

Best regards,
Helena Grulichova

User avatar
Member
Member
Bev - 8/2/2012 6:22:09 AM
   
RE:Repeater - Sort Results by Popularity
Heya,

I'm looking at popularity in terms of number of page views, so I would guess somehow grabbing the total per page from web analytics??

Thanks,

Bev

User avatar
Kentico Developer
Kentico Developer
kentico_helenag - 8/9/2012 3:18:46 AM
   
RE:Repeater - Sort Results by Popularity
Hi Bev,


It is not possible to use a simple Order by expression since we cannot know what time period you want and some more details.

You can use a repeater with custom query and access the web analytics tables to get the data. The web analytics must be enabled and working.

Please see the structure of database tables: Web analytics database tables

You can see the built-in queries in CMSDesk -> Tools -> Reporting -> select Web analytics reporting folder and one of reports -> General -> and see the queries for an inspiration.

A sample query can be:

SELECT DocumentNamePath, NodeName, NodeAliasPath, articleTitle, datestamp, smallDescription, SUM(HitsCount) FROM 
Analytics_Statistics, Analytics_DayHits, View_SDay_Articles_Joined
WHERE (StatisticsSiteID = 1) AND (StatisticsCode='pageviews') AND
(StatisticsID = HitsStatisticsID) AND (StatisticsObjectID = NodeID)
AND (StatisticsObjectCulture = DocumentCulture)
AND (HitsStartTime >= DATEADD(dd,-1, GETDATE()))

GROUP BY DocumentNamePath, NodeName, NodeAliasPath, articleTitle, datestamp, smallDescription
ORDER BY SUM(HitsCount) DESC


You can also create a custom functionality that you will add +1 to a custom field of documents by code when a page is loaded and then you can use a simple Order by condition about this field. You would also need to handle deletion of old data there.


Best regards,
Helena Grulichova

User avatar
Certified Developer 8
Certified Developer 8
Jiveabillion - 8/9/2012 10:38:16 AM
   
RE:Repeater - Sort Results by Popularity
I've implemented this functionality on nearly all of the websites I have done.

Instead of using the web analytics, I created a couple of new tables in the database (I did not use a customtable, but you could if you wanted to) that store this data

Table 1 stores individual hit information with these columns:

NodeGuid, ImpressionDateTime

Table 2 stores 1 row for each NodeGuid with a running count of views. This table was added for performance reasons so that I can query the total number of views for a document without running an expensive Count() aggregation.
Table 2 has these columns:

NodeGuid, ViewCount

I also added a trigger int he database on Table 1 that inserts/updates the records in Table 2 on Insert.

Now comes the part that puts data in there.

Because I didn't want search engine crawler hits to be included, I created a web part that uses Javascript and an Ajax webservice call to a function that I have in a webservice that I added to the project. (you can use an ashx handler instead if you want). The webpart just ads some javascript that makes a call to the web service on page load and passes the webservice the NodeGuid of CMSContext.CurrentDocument.

You don't have to do that if you don't want to, you can just make a non-visible web part that inserts a record into Table 1 any time a page is loaded.


I then have a View with this query called View_Popularity. This query calculates the popularity based on how many views per day the document has:


Select ViewCount/DaysOld as Popularity, NodeID from(
Select Case When DATEDIFF(day,T.DocumentCreatedWhen,GetDate()) = 0 then 1 else DATEDIFF(day,T.DocumentCreatedWhen,GetDate()) END as DaysOld,
Case When (C.ViewCount = 0 OR C.ViewCount IS null) then 1 else C.ViewCount END As ViewCount,T.* from View_CMS_Tree_Joined T
LEFT OUTER JOIN tPostViewCount C on C.PostID = T.NodeGUID
) PViews


Then you can have a where condition like this in your web part: (My example queries the top N most popular blog posts. Make sure you specify a value for Select top N documents or else you will get a SQL error because you can't order subqueries without a TOP N specified)


NodeID IN (Select ##TOPN## NodeID from
View_Popularity WHERE ClassName in ('cms.blogpost')
ORDER BY Popularity DESC)


This works very well for me. I hope it also works well for you.

User avatar
Certified Developer 8
Certified Developer 8
Jiveabillion - 8/9/2012 10:45:17 AM
   
RE:Repeater - Sort Results by Popularity
Note: If you want to return all results from the popularity query, you can use "TOP 100 Percent"