Pagination in union query of two page type

Pritam Gupta asked on August 17, 2017 17:46

Hi, Currently i am using kentcio 9 and i making a custom API and I want union of two page type and in it i also want pagination. When i put pagination in query so i am getting error. So can you please suggest me the better way to resolve this issue.below is my code.

Code
    DocumentQuery dqVideo = getVideoQuery(astroid).WhereEquals("vf.astroid", astroid);
    DocumentQuery dqArticle = getArticleQuery(astroid).WhereEquals("af.astroid", astroid);

    DocumentQuery resultQuery= dqVideo.Union(dqArticle);
    DataTable dt=               
    resultQuery.OrderByDescending("DocumentPublihFrom").Page(1,50).Result.Tables[0];

Recent Answers


Brenden Kehren answered on August 17, 2017 18:03

What error are you receiving?

0 votesVote for this answer Mark as a Correct answer

Pritam Gupta answered on August 18, 2017 06:12

Hello Brenden,

I am getting this error

{ "Message": "An error has occurred.", "ExceptionMessage": "[SqlHelper.ReplaceColumns]: Missing ##COLUMNS## macro in the query text '\r\nWITH AllData AS \r\n(\r\n (\r\nSELECT NodeAliasPath AS [url], C.Category AS [categoryid], 'localhost:81' AS [Domain], [thumbnail], [m3u8url], 'Video' AS [objecttype], '' AS [MainImage], C.Priority, C.recommended, [DocumentID], ISNULL(DocumentPublishFrom,DocumentCreatedWhen) AS [DocumentPublishFrom], [DocumentPublishTo], ISNULL(vf.astroid,'0') AS [astroid], '' AS [likeCount], '' AS [ViewCount], '' AS [Trending], C.VideoId AS [id], [title], ' ' AS [subtitle], [sfvtags], C.description, '' AS [DescriptionBody], [content], [mediaid], vf.ItemModifiedWhen, [ClassName], [DocumentCheckedOutVersionHistoryID]\r\nFROM View_CMS_Tree_Joined AS V WITH (NOLOCK, NOEXPAND) INNER JOIN AstroHotSpot_Video AS C WITH (NOLOCK) ON V.DocumentForeignKeyValue = C.VideoID AND V.ClassName = N'AstroHotSpot.Video' LEFT OUTER JOIN COM_SKU AS S WITH (NOLOCK) ON V.NodeSKUID = S.SKUID LEFT OUTER JOIN customtable_HotSpotVideoSave as vf ON C.mediaid = vf.sfvmediaid AND vf.astroid = @vf_astroid\r\nWHERE [NodeSiteID] = @NodeSiteID AND [DocumentPublishFrom] IS NOT NULL AND [mediaid] IS NOT NULL AND [m3u8url] IS NOT NULL AND [astroid] = @astroid AND (([DocumentCanBePublished] = 1 AND ([DocumentPublishFrom] IS NULL OR [DocumentPublishFrom] <= @Now) AND ([DocumentPublishTo] IS NULL OR [DocumentPublishTo] >= @Now)) AND [DocumentCulture] = @DocumentCulture)\r\n)\r\n\r\nUNION\r\n(\r\nSELECT NodeAliasPath AS [url], C.Categories AS [categoryid], 'localhost:81' AS [Domain], '' AS [thumbnail], '' AS [m3u8url], 'Article' AS [objecttype], REPLACE(REPLACE(C.MainImage,'~',''),'/getattachment/','http://localhost:81/getattachment/') AS [MainImage], C.Priority, C.Recommended, [DocumentID], ISNULL(DocumentPublishFrom,DocumentCreatedWhen) AS [DocumentPublishFrom], [DocumentPublishTo], ISNULL(af.astroid,'0') AS [astroid], C.FavoriteCount AS [likeCount], C.ViewCount, C.Trending, C.ArticleCMSID AS [id], ArticleTitle AS [title], ' ' AS [subtitle], C.Tags AS [sfvtags], C.Description, C.ArticleBody AS [DescriptionBody], '' AS [content], '' AS [mediaid], af.ItemModifiedWhen, [ClassName], [DocumentCheckedOutVersionHistoryID]\r\nFROM View_CMS_Tree_Joined AS V WITH (NOLOCK, NOEXPAND) INNER JOIN HotSpot_ArticleCMS AS C WITH (NOLOCK) ON V.DocumentForeignKeyValue = C.ArticleCMSID AND V.ClassName = N'HotSpot.ArticleCMS' LEFT OUTER JOIN COM_SKU AS S WITH (NOLOCK) ON V.NodeSKUID = S.SKUID LEFT OUTER JOIN customtable_HotSpotArticleSave as af ON C.ArticleCMSID = af.ArticleCmsId AND af.AstroId = @af_AstroId\r\nWHERE [NodeSiteID] = @NodeSiteID AND [DocumentPublishFrom] IS NOT NULL AND [mobile] > @mobile AND [astroid] = @astroid AND (([DocumentCanBePublished] = 1 AND ([DocumentPublishFrom] IS NULL OR [DocumentPublishFrom] <= @Now) AND ([DocumentPublishTo] IS NULL OR [DocumentPublishTo] >= @Now)) AND [DocumentCulture] = @DocumentCulture)\r\n)\r\n\r\n) \r\nSELECT , (SELECT COUNT() FROM AllData) AS [CMS_TOT] \r\nFROM AllData \r\nWHERE CMS_RN BETWEEN 3 AND 4 \r\nORDER BY CMS_RN\r\n', cannot apply the specified columns '*, ROW_NUMBER() OVER (ORDER BY [DocumentName]) AS [CMS_RN]'.", "ExceptionType": "System.NotSupportedException", "StackTrace": " at CMS.DataEngine.SqlMacroHelper.ReplaceColumns(String queryText, String columns)\r\n at CMS.DataEngine.SqlMacroHelper.ResolveMacrosInQueryText(QueryMacros queryMacros, String queryText)\r\n at CMS.DataEngine.SqlMacroHelper.ResolveQueryMacros(QueryMacros queryMacros, String queryText)\r\n at CMS.DataEngine.QueryParameters.ResolveMacrosInternal(Boolean updateQueryText)\r\n at CMS.DataEngine.GeneralConnection.ExecuteQuery(QueryParameters query)\r\n at CMS.DataEngine.GeneralConnection.ExecuteQuery(QueryParameters query, Int32& totalRecords)\r\n at CMS.DataEngine.DataQueryBase1.GetDataFromDBInternal()\r\n at CMS.DataEngine.DataQueryBase1.GetDataFromDB()\r\n at CMS.DataEngine.DataQueryBase1.GetData()\r\n at CMS.DataEngine.DataQueryBase1.get_Result()\r\n at CMS.DataEngine.ObjectQueryBase2.GetResults(IDataQuery query, Int32& totalRecords)\r\n at CMS.DataEngine.DataQueryBase1.GetDataFromDB()\r\n at CMS.DocumentEngine.DocumentQueryBase2.<GetDataFromDB>b__2()\r\n at CMS.DocumentEngine.DocumentQueryProperties.GetDataInternal(IDocumentQuery query, Func1 baseGetDataMethod, Action1 setTotalRecords)\r\n at CMS.DataEngine.DataQueryBase1.GetData()\r\n at CMS.DataEngine.ObjectQueryBase2.GetData()\r\n at CMS.DocumentEngine.DocumentQueryBase2.GetData()\r\n at CMS.DataEngine.DataQueryBase1.get_Result()\r\n at HotSpotAPI.ArticlesVideosSavedController.latestlist(Int32 page, Int32 pagesize, String astroid) in E:\\Kentico9 _hotspot_new\\Kentico9 _hotspot_new\\HotSpotAPI\\ArticlesVideosSavedController.cs:line 237\r\n at HotSpotAPI.ArticlesVideosSavedController.list(Int32 page, Int32 pagesize) in E:\\Kentico9 _hotspot_new\\Kentico9 _hotspot_new\\HotSpotAPI\\ArticlesVideosSavedController.cs:line 219\r\n at lambda_method(Closure , Object , Object[] )\r\n at System.Web.Http.Controllers.ReflectedHttpActionDescriptor.ActionExecutor.<>c__DisplayClass10.<GetExecutor>b__9(Object instance, Object[] methodParameters)\r\n at System.Web.Http.Controllers.ReflectedHttpActionDescriptor.ExecuteAsync(HttpControllerContext controllerContext, IDictionary2 arguments, CancellationToken cancellationToken)\r\n--- End of stack trace from previous location where exception was thrown ---\r\n at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)\r\n at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)\r\n at System.Web.Http.Controllers.ApiControllerActionInvoker.

0 votesVote for this answer Mark as a Correct answer

Trevor Fayas answered on August 18, 2017 14:02

have you tried putting the column macro into the query like it recommends? wrapping the results often helps:

select ##topn## ##columns## from (
[your query here]) as fullquery where ##where## order by ##orderby##
0 votesVote for this answer Mark as a Correct answer

Brenden Kehren answered on August 18, 2017 14:08

The error has 2 specific messages in it:

  • [SqlHelper.ReplaceColumns]: Missing ##COLUMNS## macro in the query text
  • cannot apply the specified columns '*, ROW_NUMBER() OVER (ORDER BY [DocumentName]) AS [CMS_RN]'

There is also a spelling error in your code for the column name:

DataTable dt= resultQuery.OrderByDescending("DocumentPublihFrom").Page(1,50).Result.Tables[0];

Should be:

DataTable dt= resultQuery.OrderByDescending("DocumentPublishFrom").Page(1,50).Result.Tables[0];

Lastly, what code do these methods have in them and what is the input paramater value:

  • getVideoQuery()
  • getArticleQuery()
0 votesVote for this answer Mark as a Correct answer

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