How to create "Most Read" block of a website in Kentico API

Nelson Nebre asked on June 28, 2016 09:14

Hi guys, I have another question with you guy ? Everytime I solved a problem there's another problem occuring. Our problem is anyone here created a "Most Read" block in kentico using the Kentico API ? BTW we developing our site using MVC approach.

Correct Answer

Dawid Jachnik answered on June 28, 2016 09:30

Hello,

I think it cannot be done with API. You need to use "custom" query, because this query is taken from the reports Page Views. Here's my function

            private DataSet GetTopVisistedPages(int taskSiteID, string nodeAliasPath, int topN, string culture, string className = "bg.faq")
        {
            QueryParameters qp = new QueryParameters(@"DECLARE @PaveViews TABLE
            (
              PagePath NVARCHAR(500),
              ObjectID INT,
              Pageviews INT,
              Percents DECIMAL(10,2),
              Average INT  
            )

            DECLARE @Sum DECIMAL;
            declare @CMSContextCurrentSiteID int;

            set @CMSContextCurrentSiteID=" + taskSiteID + @";


            SELECT @Sum =   
              SUM(HitsCount) 
              FROM Analytics_Statistics
              INNER JOIN Analytics_YearHits ON Analytics_YearHits.HitsStatisticsID = Analytics_Statistics.StatisticsID
              LEFT JOIN View_CMS_Tree_Joined ON View_CMS_Tree_Joined.NodeID = Analytics_Statistics.StatisticsObjectID  AND StatisticsObjectCulture = DocumentCulture
              WHERE (StatisticsSiteID = @CMSContextCurrentSiteID)
              AND (StatisticsCode='pageviews')


            INSERT INTO @PaveViews (PagePath,ObjectID,PageViews,Percents)
              SELECT TOP 100 StatsWithPath.NodeAliasPath, StatsWithPath.ObjectID, SUM(StatsWithPath.HitsCount) AS PageViews, (SUM(StatsWithPath.HitsCount)/@Sum)*100 AS Percents FROM
              (
                SELECT
                 -- When NodeAliasPath is empty, use value from StatisticsObjectName instead. This could happed for example when document does not longer exist in one of the cultures.
                 -- In this case row can't be joined with View_CMS_Tree_Joined.
                 CASE
                  WHEN NodeAliasPath = '' OR NodeAliasPath IS NULL THEN StatisticsObjectName
                  ELSE NodeAliasPath
                 END AS NodeAliasPath, StatisticsObjectID AS ObjectID, HitsCount 
                 FROM Analytics_Statistics
                 INNER JOIN Analytics_YearHits ON Analytics_YearHits.HitsStatisticsID = Analytics_Statistics.StatisticsID
                 LEFT JOIN View_CMS_Tree_Joined ON Analytics_Statistics.StatisticsObjectID = View_CMS_Tree_Joined.NodeID AND StatisticsObjectCulture = DocumentCulture
                 WHERE (StatisticsSiteID = @CMSContextCurrentSiteID) AND (StatisticsCode = 'pageviews')
              ) AS StatsWithPath
              GROUP BY StatsWithPath.NodeAliasPath, StatsWithPath.ObjectID
              ORDER BY PageViews DESC

             UPDATE @PaveViews SET Average = (SELECT SUM(HitsValue)/SUM(HitsCount) FROM Analytics_YearHits JOIN
                  Analytics_Statistics ON HitsStatisticsID = StatisticsID
                  WHERE StatisticsObjectID = objectID
                    AND StatisticsCode ='avgtimeonpage' AND StatisticsSiteID = @CMSContextCurrentSiteID
                   )

             SELECT top " + topN + @" Pageviews,t.NodeAliasPath,t.DocumentName
               FROM @PaveViews p RIGHT JOIN View_CMS_Tree_Joined t ON p.ObjectID=t.NodeID
               where
               t.NodeAliasPath like N'" + nodeAliasPath + @"/%' and t.DocumentCulture=N'" + culture + @"' and t.ClassName=N'" + className + @"'
               and
               ([DocumentCanBePublished] = 1 AND ([DocumentPublishFrom] IS NULL OR [DocumentPublishFrom] <= GETDATE()) AND ([DocumentPublishTo] IS NULL OR [DocumentPublishTo] >= GETDATE()))
                ORDER BY PageViews DESC", null, QueryTypeEnum.SQLQuery);

            return ConnectionHelper.ExecuteQuery(qp);
        }
1 votesVote for this answer Unmark Correct answer

Recent Answers


Nelson Nebre answered on June 28, 2016 10:05

Thanks Sir Dawid, but can you give an example on how to use that custom query ? Thanks in advance.

0 votesVote for this answer Mark as a Correct answer

Dawid Jachnik answered on June 28, 2016 10:49 (last edited on June 28, 2016 10:49)

You can use my function as a datasource of your repeater or some other listing control. You need to specify parameters:

  • taskSiteId - is your site id
  • nodeAliasPath - from which you want to retrive the most read (for all documents leave empty)
  • topN - top many top visited pages you want
  • culture - specify from which culture you want to retrive the documents
  • className - class name of page type you want to check
0 votesVote for this answer Mark as a Correct answer

Nelson Nebre answered on June 28, 2016 12:12

I'll try, Thank you so much sir.

0 votesVote for this answer Mark as a Correct answer

Dawid Jachnik answered on June 28, 2016 12:54

Don't forget to mark the correct answer if it helps :)

0 votesVote for this answer Mark as a Correct answer

Nelson Nebre answered on June 29, 2016 07:22

Sir do I need to change this line ? Because an error occorred said that the "Unknown column: DocumentCanBePublished".

 ([DocumentCanBePublished] = 1 AND ([DocumentPublishFrom] IS NULL OR [DocumentPublishFrom] <= GETDATE()) AND ([DocumentPublishTo] IS NULL OR [DocumentPublishTo] >= GETDATE()))
0 votesVote for this answer Mark as a Correct answer

Dawid Jachnik answered on June 29, 2016 12:59

Replace the

 ([DocumentCanBePublished] = 1 AND ([DocumentPublishFrom] IS NULL OR [DocumentPublishFrom] <= GETDATE()) AND ([DocumentPublishTo] IS NULL OR [DocumentPublishTo] >= GETDATE()))

with

Published=1

And mark the correct answer :)

0 votesVote for this answer Mark as a Correct answer

   Please, sign in to be able to submit a new answer.