DocumentHelper using joins to get page views

Trevor Chinn asked on October 3, 2016 13:59

Hello,

I am currently trying to convert a custom query that I have running for a repeater into code using the DocumentHelper class, however I have run into some brick walls. It is a relatively simple issue:

Get pages (of a custom page type) sorted by views (in the future it will be weekly, but right now the traffic is very low so we have to use the lifetime total to have any meaning).

Using macros, devnet and a bit of fiddling around in MS SQL Server management studio I was able to piece together the following custom query:

SELECT TOP 5 NodeAliasPath, SUM(HitsCount) as Hits, C.summary_img, C.summary_text, C.title, V.NodeAlias, V.NodeGUID, V.DocumentName 
FROM Analytics_Statistics, Analytics_DayHits, 
View_CMS_Tree_Joined AS V
 WITH (NOLOCK, NOEXPAND) 
 INNER JOIN custom_my_pr_article AS C 
  WITH (NOLOCK) ON DocumentForeignKeyValue = C.My_pr_articleID AND ClassName =     N'custom.my_pr_article' 
WHERE StatisticsCode='pageviews' AND 
StatisticsID = HitsStatisticsID AND StatisticsObjectID = NodeID AND ClassName =     'custom.my_pr_article'
GROUP BY NodeAliasPath, C.summary_img, C.summary_text, C.title, V.NodeAlias, v.NodeGUID,     V.DocumentName 
ORDER BY Hits DESC

-- Where condition: Add "HitsStartTime" "HitsEndTime"

I now need to use this same logic on a Ajax API endpoint I am making, and I thought it would be as simple as adding a few join clauses and selecting the right columns, but apparently my understanding is severely lacking in this department. As a simple test, I decided to go with just a single join, but it all fell apart really quickly. Here is the code I attempted:

var collection = DocumentHelper.
                GetDocuments("custom.my_pr_article").
                Path("/Articles", PathTypeEnum.Children).
                Columns("NodeID", "title", "summary_text", "summary_img", "DocumentCreatedWhen", "DocumentID", "DocumentCreatedByUserID", "NodeAliasPath", "DocumentTags").
                OrderBy("DocumentCreatedWhen Desc").
                Page(x.Offset, x.BatchSize).
                Where(whereCondition).
                Source(s => s.LeftJoin("Analytics_Statistics", "NodeID", "StatisticsObjectID")).
                //Source(s => s.LeftJoin("Analytics_DayHits", "HitsStatisticsID", "StatisticsID")).
                Select(t => new ReturnObject(t));

It fails because it results in the following SQL:

DECLARE @NodeAliasPath nvarchar(max) = N'/Articles/%';


WITH AllData AS 
(
SELECT [NodeID], [title], [summary_text], [summary_img], [DocumentCreatedWhen], [DocumentID], [DocumentCreatedByUserID], [NodeAliasPath], [DocumentTags], [ClassName], [DocumentCheckedOutVersionHistoryID], ROW_NUMBER() OVER (ORDER BY [DocumentCreatedWhen] DESC) AS [CMS_RN] 
FROM View_CMS_Tree_Joined AS V WITH (NOLOCK, NOEXPAND) INNER JOIN custom_my_pr_article AS C WITH (NOLOCK) ON V.DocumentForeignKeyValue = C.My_pr_articleID AND V.ClassName = N'custom.my_pr_article' LEFT OUTER JOIN COM_SKU AS S WITH (NOLOCK) ON V.NodeSKUID = S.SKUID LEFT OUTER JOIN Analytics_Statistics ON S.NodeID = Analytics_Statistics.StatisticsObjectID 
WHERE (1 = 1) AND [NodeAliasPath] LIKE @NodeAliasPath
) 
SELECT *, (SELECT COUNT(*) FROM AllData) AS [CMS_TOT] 
FROM AllData 
WHERE CMS_RN BETWEEN 1 AND 60 
ORDER BY CMS_RN

It tries to join on S.NodeID when it should be joining on V.NodeID... What am I doing wrong?

I am probably doing it completely wrong by attempting to join these in the documenthelper when I didn't do so in the original SQL, but I have been looking at it for too long and have lost the ability to reason it out. Any and all assistance would be much appreciated.

Thanks

Trevor

Correct Answer

Richard Sustek answered on October 4, 2016 08:29

You can actually specify which table should be used as the source using e.g

V.NodeID instead of just NodeID

The best practice is also to avoid specifying the table/view directly so instead of using

LeftJoin("Analytics_Statistics", "V.NodeID", "StatisticsObjectID")

You can use:

LeftJoin<StatisticsInfo>("V.NodeID", "StatisticsObjectID")

The full API call which should work in your case is:

var collection = DocumentHelper.
                GetDocuments("custom.my_pr_article").
                Path("/Articles", PathTypeEnum.Children).
                Columns("NodeID", "title", "summary_text", "summary_img", "DocumentCreatedWhen", "DocumentID", "DocumentCreatedByUserID", "NodeAliasPath", "DocumentTags").
                OrderBy("DocumentCreatedWhen Desc").
                Page(x.Offset, x.BatchSize).
                Where(whereCondition).
                Source(s => s.LeftJoin<StatisticsInfo>("V.NodeID", "StatisticsObjectID")).
                Select(t => new ReturnObject(t));

1 votesVote for this answer Unmark Correct answer

Recent Answers

Trevor Fayas answered on October 3, 2016 18:02 (last edited on October 3, 2016 18:04)

This is a bit of a shot in the dark, but have you tried using a differnet letter in your lynq? "S" is commonly used in Kentico for "Sku" to do joins on the sku table, maybe try

Source(u => u.LeftJoin("Analytics_Statistics", "NodeID", "StatisticsObjectID"))

if that doe'snt work, try putting a full reference to NodeID

Source(s => s.LeftJoin("Analytics_Statistics", "View_CMS_Tree_Joined.NodeID", "StatisticsObjectID"))

0 votesVote for this answer Mark as a Correct answer

Anton Grekhovodov answered on October 3, 2016 19:30

Hi both,

It doesn't matter how you call a variable inside Linq query (S isn't connected with SKU). LEFT OUTER JOIN COM_SKU exists in query, because there are some database changes in Kentico API. Read more here

So, Trevor, try to use LeftJoin<YourType>() or LeftJoin() methods and add a full reference on the view or just alias to columns

1 votesVote for this answer Mark as a Correct answer

Trevor Chinn answered on October 4, 2016 10:55

Thanks guys for your input. I read both Anton's and Trevor's answers and they helped a bit, but I couldn't get it working after three more hours, so using what I found on this page, I just build a custom query solution:

http://devnet.kentico.com/questions/how-to-create-most-read-block-of-a-website-in-kentico-api

So in essence I have circumvented the issue. I will try Richard's solution when I get some time, but this was supposed to be finished on Monday so I am rather pressed for time, and for now "working" is good enough :)

QueryParameters qp = new QueryParameters(@"SELECT NodeAliasPath as url, SUM(HitsCount) as ViewsTotal, C.summary_img, C.summary_text, C.title, FORMAT(DocumentCreatedWhen, 'yyyy.M.dd') as published_date
                FROM Analytics_Statistics, Analytics_DayHits, 
                View_CMS_Tree_Joined AS V
                 WITH (NOLOCK, NOEXPAND) 
                 INNER JOIN custom_my_pr_article AS C 
                  WITH (NOLOCK) ON DocumentForeignKeyValue = C.my_pr_articleID AND ClassName = N'custom.my_pr_article' 
                WHERE StatisticsCode='pageviews' AND 
                StatisticsID = HitsStatisticsID AND StatisticsObjectID = V.NodeID AND ClassName = 'custom.my_pr_article' AND " + whereCondition + @"
                GROUP BY NodeAliasPath, C.summary_img, C.summary_text, C.title, V.NodeAlias, v.NodeGUID, V.DocumentName, DocumentCreatedWhen
                ORDER BY ViewsTotal DESC
                OFFSET " + (offset * batchSize) + @" ROWS
                FETCH NEXT " + batchSize + @" ROWS ONLY", null, QueryTypeEnum.SQLQuery);
            //This method of offset is inefficient
            //Should use this way: https://blog.jooq.org/2013/10/26/faster-sql-paging-with-jooq-using-the-seek-method/

            var qp_results = ConnectionHelper.ExecuteQuery(qp);

            //For testing -> Later when the domain is chosen we can remove this 
            string baseURL = "/Kentico9_3";
            foreach (DataRow row in qp_results.Tables[0].Rows)
            {
                row["url"] = baseURL + row["url"];
                row["summary_img"] = row["summary_img"].ToString().Replace("~", baseURL);
            }
            //Returns cleanly as a JSON object for the API endpoint
            return qp_results.Tables[0];

Your help is always appreciated! I hope in time to be able to assist other users in the same way ~

0 votesVote for this answer Mark as a Correct answer