DocumentHelper.GetDocuments() with Columns() method doesn't limit columns returned by SQL correctly

Wayne Iobst asked on October 27, 2019 16:17

Hello,

When we use the DocumentHelper.GetDocuments() with the Columns() method, e.g.

var resultsQuery = DocumentHelper.GetDocuments()
    .Types( "Company.Type1", "Company.Type2" )
    .Columns( "DocumentTitle", "NodeAliasPath" )
    .WhereIn( "NodeAliasPath", new string[] { "/home/page1", "/home/page2" } );   

var sql = resultsQuery.GetFullQueryText();

The generated SQL selects all columns from the Type1 and Type2 tables, not just the ones I've specified above (including the full HTML of the page). I feel like I must be missing something here or this is just a major bug. If I change the query to use the DocumentHelper.GetDocuments("Company1.Type1") method instead, again specifying the columns I want, then the SQL query correctly limits the columns returned. But I'd rather not have to iterate over each page type performing individual queries and combining the results or use some manual UNION query to get the result I want. According to the documentation, the above query should work.

We are using the latest version of Kentico (12 SP, version 12.0.2000.8) on an MVC site.

What am I missing or what do I need to change to get the expected behavior?

Recent Answers


vasu yerramsetti answered on October 28, 2019 06:03

I think it requires specified Path. Please check below URL.

https://docs.kentico.com/k12/custom-development/working-with-pages-in-the-api

0 votesVote for this answer Mark as a Correct answer

Roman Hutnyk answered on October 28, 2019 13:39

My guess would be that DocumentHelper works with documents and operates data from Tree, Document and coupled data table. If you don't need coupled data try using TreeProvider.

Another workaround would be to use custom query.

0 votesVote for this answer Mark as a Correct answer

Wayne Iobst answered on October 28, 2019 15:43

I already tried the TreeProvider and had the same result. I'm not really interested in finding a workaround - I know I can use a UNION to get the result I want, but its rather tedious to do that every time I want to write a query like this. The documentation clearly makes it sound like the code above should generate SQL that only returns the columns specified by the Column() method, and its just not working that way. Can someone please confirm this looks to be bug or point me to some documentation for how to change the code above to correctly generate SQL that limits the columns returned?

At the very least they should update the documentation to point out this limitation of the Columns() method, but ideally they would just get it to work the way its supposed to.

0 votesVote for this answer Mark as a Correct answer

Peter Mogilnitski answered on October 28, 2019 16:02 (last edited on October 28, 2019 17:33)

Specify OnSite in your query. Here is the example from this article

// Get news title from /News section and article title from /Community section together with document name column
var documents = DocumentHelper.GetDocuments()
                              .Type("CMS.News", q => q
                                  .Columns("NewsTitle")
                                  .Path("/News", PathTypeEnum.Children))
                              .Type("CMS.SimpleArticle", q => q
                                  .Columns("ArticleTitle")
                                  .Path("/Community", PathTypeEnum.Children))
                              .Columns("DocumentName")
                              .OnSite("CorporateSite");
0 votesVote for this answer Mark as a Correct answer

Wayne Iobst answered on October 28, 2019 18:01

Nope, that doesn't help.

0 votesVote for this answer Mark as a Correct answer

Peter Mogilnitski answered on October 28, 2019 21:41 (last edited on October 28, 2019 21:50)

Yes. You are right if it does multiple types - doesn't seem to be doing columns correctly in sql, although if you do for single type - it will work. i.e.

var text = DocumentHelper.GetDocuments("CMS.MenuItem")
                //.Types(new string[] {"CMS.MenuItem", "CMS.BookingEvent"})
                .Path("/Community/Events/", PathTypeEnum.Section)
                .OnSite("CorporateSite")
                .Columns(new string[] {"DocumentName", "NodeAliasPath"})
                .Culture("en-us")
                .GetFullQueryText();

Gives this:

DECLARE @NodeSiteID int = 2;
DECLARE @NodeAliasPath nvarchar(max) = N'/Community/Events/%';
DECLARE @NodeAliasPath1 nvarchar(max) = N'/Community/Events';
DECLARE @DocumentCulture nvarchar(max) = N'en-us';

SELECT [DocumentName], [NodeAliasPath], [ClassName], [DocumentCheckedOutVersionHistoryID]
FROM View_CMS_Tree_Joined AS V WITH (NOLOCK, NOEXPAND) INNER JOIN CONTENT_MenuItem AS C WITH (NOLOCK) ON [V].[DocumentForeignKeyValue] = [C].[MenuItemID] AND V.ClassName = N'CMS.MenuItem' LEFT OUTER JOIN COM_SKU AS S WITH (NOLOCK) ON [V].[NodeSKUID] = [S].[SKUID]
WHERE [NodeSiteID] = @NodeSiteID AND (([NodeAliasPath] LIKE @NodeAliasPath OR [NodeAliasPath] = @NodeAliasPath1) AND [DocumentCulture] = @DocumentCulture)

Although multiples types:

var queryText = DocumentHelper.GetDocuments()
                .Types(new string[] {"CMS.MenuItem", "CMS.BookingEvent"})
                .Path("/Community/Events/", PathTypeEnum.Section)
                .OnSite("CorporateSite")
                .Columns(new string[] {"DocumentName", "NodeAliasPath"})
                .Culture("en-us")
                .GetFullQueryText();

gives this:

DECLARE @NodeSiteID int = 2;
DECLARE @NodeAliasPath nvarchar(max) = N'/Community/Events/%';
DECLARE @NodeAliasPath1 nvarchar(max) = N'/Community/Events';
DECLARE @DocumentCulture nvarchar(max) = N'en-us';

    SELECT *
    FROM (
    (
    SELECT [MenuItemID], [MenuItemName], [MenuItemTeaserImage], [MenuItemGroup], NULL AS [BookingEventID], NULL AS [EventName], NULL AS [EventSummary], NULL AS [EventDetails], NULL AS [EventLocation], NULL AS [EventDate], NULL AS [EventEndDate], NULL AS [EventAllDay], NULL AS [EventCapacity], NULL AS [EventAllowRegistrationOverCapacity], NULL AS [EventOpenFrom], NULL AS [EventOpenTo], NULL AS [EventLogActivity], [DocumentName], [NodeAliasPath], ROW_NUMBER() OVER (ORDER BY DocumentName) AS [CMS_SRN], 0 AS [CMS_SN], 'cms.document.cms.menuitem' AS [CMS_T], [ClassName], [DocumentCheckedOutVersionHistoryID]
    FROM View_CMS_Tree_Joined AS V WITH (NOLOCK, NOEXPAND) INNER JOIN CONTENT_MenuItem AS C WITH (NOLOCK) ON [V].[DocumentForeignKeyValue] = [C].[MenuItemID] AND V.ClassName = N'CMS.MenuItem' LEFT OUTER JOIN COM_SKU AS S WITH (NOLOCK) ON [V].[NodeSKUID] = [S].[SKUID]
    WHERE [NodeSiteID] = @NodeSiteID AND (([NodeAliasPath] LIKE @NodeAliasPath OR [NodeAliasPath] = @NodeAliasPath1) AND [DocumentCulture] = @DocumentCulture)
    )

    UNION ALL
    (
    SELECT NULL AS [MenuItemID], NULL AS [MenuItemName], NULL AS [MenuItemTeaserImage], NULL AS [MenuItemGroup], [BookingEventID], [EventName], [EventSummary], [EventDetails], [EventLocation], [EventDate], [EventEndDate], [EventAllDay], [EventCapacity], [EventAllowRegistrationOverCapacity], [EventOpenFrom], [EventOpenTo], [EventLogActivity], [DocumentName], [NodeAliasPath], ROW_NUMBER() OVER (ORDER BY DocumentName) AS [CMS_SRN], 1 AS [CMS_SN], 'cms.document.cms.bookingevent' AS [CMS_T], [ClassName], [DocumentCheckedOutVersionHistoryID]
    FROM View_CMS_Tree_Joined AS V WITH (NOLOCK, NOEXPAND) INNER JOIN CONTENT_BookingEvent AS C WITH (NOLOCK) ON [V].[DocumentForeignKeyValue] = [C].[BookingEventID] AND V.ClassName = N'CMS.BookingEvent' LEFT OUTER JOIN COM_SKU AS S WITH (NOLOCK) ON [V].[NodeSKUID] = [S].[SKUID]
    WHERE [NodeSiteID] = @NodeSiteID AND (([NodeAliasPath] LIKE @NodeAliasPath OR [NodeAliasPath] = @NodeAliasPath1) AND [DocumentCulture] = @DocumentCulture)
    )

    ) AS SubData
    ORDER BY CMS_SRN, CMS_SN
0 votesVote for this answer Mark as a Correct answer

Peter Mogilnitski answered on October 28, 2019 22:05

Actually I got it working - you have to specify the columns twice

var queryText = DocumentHelper.GetDocuments()
    .Type("CMS.MenuItem", q => q
        .Columns("DocumentName", "NodeAliasPath")
        .Path("/Community/Events/", PathTypeEnum.Section))
    .Type("CMS.BookingEvent", q => q
        .Columns("DocumentName", "NodeAliasPath")
        .Path("/Community/Events/", PathTypeEnum.Section))
    .OnSite("CorporateSite")
    .Columns("DocumentName", "NodeAliasPath")
    .GetFullQueryText();

This gives me:

DECLARE @NodeSiteID int = 2;
DECLARE @NodeAliasPath nvarchar(max) = N'/Community/Events/%';
DECLARE @NodeAliasPath1 nvarchar(max) = N'/Community/Events';
DECLARE @DocumentCulture nvarchar(max) = N'en-US';

SELECT *
FROM (
(
SELECT [DocumentName], [NodeAliasPath], ROW_NUMBER() OVER (ORDER BY DocumentName) AS [CMS_SRN], 0 AS [CMS_SN], 'cms.document.cms.menuitem' AS [CMS_T], [ClassName], [DocumentCheckedOutVersionHistoryID]
FROM View_CMS_Tree_Joined AS V WITH (NOLOCK, NOEXPAND) INNER JOIN CONTENT_MenuItem AS C WITH (NOLOCK) ON [V].[DocumentForeignKeyValue] = [C].[MenuItemID] AND V.ClassName = N'CMS.MenuItem' LEFT OUTER JOIN COM_SKU AS S WITH (NOLOCK) ON [V].[NodeSKUID] = [S].[SKUID]
WHERE [NodeSiteID] = @NodeSiteID AND (([NodeAliasPath] LIKE @NodeAliasPath OR [NodeAliasPath] = @NodeAliasPath1) AND [DocumentCulture] = @DocumentCulture)
)

UNION ALL
(
SELECT [DocumentName], [NodeAliasPath], ROW_NUMBER() OVER (ORDER BY DocumentName) AS [CMS_SRN], 1 AS [CMS_SN], 'cms.document.cms.bookingevent' AS [CMS_T], [ClassName], [DocumentCheckedOutVersionHistoryID]
FROM View_CMS_Tree_Joined AS V WITH (NOLOCK, NOEXPAND) INNER JOIN CONTENT_BookingEvent AS C WITH (NOLOCK) ON [V].[DocumentForeignKeyValue] = [C].[BookingEventID] AND V.ClassName = N'CMS.BookingEvent' LEFT OUTER JOIN COM_SKU AS S WITH (NOLOCK) ON [V].[NodeSKUID] = [S].[SKUID]
WHERE [NodeSiteID] = @NodeSiteID AND (([NodeAliasPath] LIKE @NodeAliasPath OR [NodeAliasPath] = @NodeAliasPath1) AND [DocumentCulture] = @DocumentCulture)
)

) AS SubData
ORDER BY CMS_SRN, CMS_SN
0 votesVote for this answer Mark as a Correct answer

Wayne Iobst answered on October 28, 2019 22:28

Peter - Thanks for looking into this. The second form of your query with multiple Type() calls instead of one Types() call is probably as close as I can get it. But if you have an arbitrary number of types that you want to use (in our case we dynamically find all types that derive from a certain base class and pass that into the Types() method), then you wouldn't be able to use a static query. Having said that, I was able to get it to work by looping over all the types in the list and calling .Type(types[i], q => q.Columns("DocumentName", "NodeAliasPath")) for each loop, building the query up and then finally running it by calling ToList(). This works but is pretty tedious to write. I'm going to look into adding an extension method to make this easier to use.

But I still think the documentation should be updated to clarify the requirements for using the Columns() method or they should just change the implementation so that it works correctly when using Types() together with Columns().

0 votesVote for this answer Mark as a Correct answer

Wayne Iobst answered on October 29, 2019 00:00

For those interested, a quick and dirty extension method based on Peter's suggestion:

public static class MultiDocumentQueryExtensions
{
    public static MultiDocumentQuery TypesAndColumns( 
        this MultiDocumentQuery multiDocumentQuery, 
        IEnumerable<string> types, params string[] columns )
    {
        foreach( string type in types )
        {
            multiDocumentQuery = multiDocumentQuery.Type( type, q => q.Columns( columns ) );
        }

        multiDocumentQuery.Columns( columns );

        return multiDocumentQuery;
    } 
}

Example usage:

var documents = DocumentHelper.GetDocuments()
    .TypesAndColumns( new string[] { "Type1", "Type2" },
        nameof( TreeNode.ClassName ),
        nameof( TreeNode.NodeAliasPath ),
        nameof( TreeNode.NodeAlias ),
        nameof( TreeNode.NodeGUID ) )
    .Path( "/Products/", PathTypeEnum.Children );
0 votesVote for this answer Mark as a Correct answer

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