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.
Columns("NodeID", "title", "summary_text", "summary_img", "DocumentCreatedWhen", "DocumentID", "DocumentCreatedByUserID", "NodeAliasPath", "DocumentTags").
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]
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.