MultiDocumentQuery Union

Laura Frese asked on March 25, 2017 21:02

Individually these two queries return the right set of data. When I do a union, all of the pages in the site are returned, not just the queried document type, path selected, & where cond. anybody have any insight on this?

        var pages = DocumentHelper.GetDocuments()
            .Columns("DocumentName,Image,Content,Keyword")
            .Types(PageType)
            .Where("Keyword In ('key1','key2')")
            .Path(CurrentDocument.NodeAliasPath, PathTypeEnum.Children)
            .OnSite(CurrentSite.SiteName)
            .Culture(CurrentDocument.DocumentCulture)
        .Union(
                 DocumentHelper.GetDocuments()
                    .Columns("DocumentName,Image,Content,Keyword")
                    .Types(PageType)
                    .Where("Keyword = 'standard'")
                    .Path(CurrentDocument.NodeAliasPath, PathTypeEnum.Children)
                    .OnSite(CurrentSite.SiteName)
                    .Culture(CurrentDocument.DocumentCulture)
        );

Recent Answers

Anton Grekhovodov answered on March 26, 2017 12:47

Hi Laura,
It's not clear for me, why you use Union query here (to order items?), because the queries are absolutely the same and you can group your where conditions

DocumentHelper.GetDocuments(PageType)
        .Columns("DocumentName,Image,Content,Keyword")
        .WhereIn("Keyword", new[] { "key1", "key2", "standard" })
        .Path(CurrentDocument.NodeAliasPath, PathTypeEnum.Children)
        .OnSite(CurrentSite.SiteName)
        .Culture(CurrentDocument.DocumentCulture);

It will be the same as yours code. Also you can add .Distinct(); to avoid duplicates.

0 votesVote for this answer Mark as a Correct answer

Laura Frese answered on March 27, 2017 08:02

Thank you Anton. Yes I needed the non standard items to be first in the list

0 votesVote for this answer Mark as a Correct answer

Anton Grekhovodov answered on March 27, 2017 08:38

But if you add OrderBy("Keyword") to your query, it will be correct sorting, because rows with 'key1' or 'key2' will be earlier in result than rows with 'standard' key.

0 votesVote for this answer Mark as a Correct answer

Laura Frese answered on March 27, 2017 19:13

Unless one of the keywords start with a letter > s

0 votesVote for this answer Mark as a Correct answer

Anton Grekhovodov answered on March 28, 2017 06:40

Of course) I've checked your query with Union and it doesn't work for me too (Kentico 8.2, Kentico 9)
So to solve it you can:
1)Add the following order by .OrderBy("case when Keyword In ('key1', 'key2') then 0 else 1 end") to my expression above
2) Use linq to reorder result items

0 votesVote for this answer Mark as a Correct answer

Pritam Gupta answered on August 17, 2017 17:28 (last edited on August 17, 2017 17:39)

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];
0 votesVote for this answer Mark as a Correct answer

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