Kentico 8 Technology – DocumentQuery API

   —   
If you like the concept of DataQuery API discussed in previous articles by Martin Hejtmanek, you should definitely read this article about the benefits of new document API using DataQuery.
We decided to leverage the concept of querying data from the Kentico database in the documents API for two main reasons. The first one was the various performance issues that we were facing and the concept that allowed us to solve them – more will be mentioned in this article later. The second reason was to improve the usability of the API and provide a fluent version of documents API. You will find several examples of the code in this article.

All of these aspects led us to create a DocumentQuery that is built on top of the ObjectQuery. This means that it can leverage all of the features that bring the base query and add additional features related to the documents.

The DocumentQuery is used for getting single document type data. Because you need to query multiple types at once very often, the MultiDocumentQuery was built to provide support for such querying. Below you can find the schema of multi-queries hierarchy, which is similar to the one mentioned in Martin’s article.

Inheritance.png

The concept of MultiQuery is all about providing support for querying data from multiple sources. Several types are combined into a single query, which could be parameterized by global parameters, data could be paged etc. In this article, I will only scratch the surface of all the capabilities that the multi-query brings. Martin will go into more details on this matter in one of his next articles about the queries concept.

Old way of getting documents data

First off, let me go through the different ways you could get documents from previous versions and when they were usually used.
In the past, if you needed to get documents with data published on a live site, you used the TreeProvider class where no version data is applied:

// Create instance of provider var provider = new TreeProvider(); // Prepare parameters var siteName = "CorporateSite"; var aliasPath = "/News/%"; var cultureCode = "en-us"; var combineWithDefaultCulture = false; var classNames = "CMS.News"; // Get documents var news = provider.SelectNodes(siteName, aliasPath, cultureCode, combineWithDefaultCulture, classNames, null, null, TreeProvider.ALL_LEVELS, true);


On the other hand when you needed to get data about latest versions (typically when a workflow is applied), the DocumentHelper class was fully suited to your needs:

// Create instance of provider var provider = new TreeProvider(); // Prepare parameters var siteName = "CorporateSite"; var aliasPath = "/News/%"; var cultureCode = "en-us"; var combineWithDefaultCulture = false; var classNames = "CMS.News"; // Get documents var news = DocumentHelper.GetDocuments(siteName, aliasPath, cultureCode, combineWithDefaultCulture, classNames, null, null, TreeProvider.ALL_LEVELS, true, provider);


Please note this way is deprecated by starting version 8 and should not be used anymore.

The new way of getting documents data

The DocumentQuery is an addition to the existing API and can be used for both scenarios. Therefore, you can find the methods for getting query in, both, the TreeProvider and DocumentHelper classes. The same rule applies here – use the TreeProvider for published data and the DocumentHelper for the latest data. Actually, both methods return query instance, but are predefined to get the published or latest data.

I highly recommend using this approach in your code, not only because of all the benefits that it brings, but also for the upgrade-friendly path in future versions.

Here’s the list of available methods to get the instance of a document query:
  • public MultiDocumentQuery TreeProvider.SelectNodes() – use this method if you want to get several types
  • public DocumentQuery TreeProvider.SelectNodes(string className) – use this method if you work with a single type specified by className parameter
  • public static MultiDocumentQuery DocumentHelper.GetDocuments() – use this method if you want to get several types
  • public static DocumentQuery DocumentHelper. GetDocuments(string className) – use this method if you work with a single type specified by className parameter
The code needed to get the same set of data, as seen in previous example, would look like this:

// Get documents var news = DocumentHelper.GetDocuments("CMS.News") .OnSite("CorporateSite") .Path("/News", PathTypeEnum.Children) .Culture("en-us") .CombineWithDefaultCulture(false);


As you can see by using the new approach, the code is much more readable and understandable.

Single document type data

Now let’s take a look at how you can parameterize the DocumentQuery.

Path methods

Compared to the existing API, there are more capabilities available for specifying the section of the documents. You can, for example, get the whole section of documents including the parent:

// Get documents from /Services section including parent page var documents = DocumentHelper.GetDocuments("CMS.MenuItem") .OnSite("CorporateSite") .Path("/Services", PathTypeEnum.Section);


Or, you can even exclude a sub-section:

// Get news items from /News section except the Archive sub-section var news = DocumentHelper.GetDocuments("CMS.News") .OnSite("CorporateSite") .Path("/News/%") .ExcludePath("/News/Archive", PathTypeEnum.Section);


The path can be specified the old way by using SQL-like syntax “/Path/%”, or you can leverage the PathTypeEnum enumeration to define the type.

Published documents

Typically, when getting documents from a live site, you want to get only published documents:

// Get published news items from /News section var news = DocumentHelper.GetDocuments("CMS.News") .OnSite("CorporateSite") .Path("/News", PathTypeEnum.Children) .Published();


The Published method has an optional parameter that can be used, when set to false, to even get the documents that have not been published.

Culture methods

When writing code, especially for a multilingual site, you need to specify the culture of the documents. With DocumentQuery you can specify even more cultures to work with all at once:

// Get English and Czech language versions of news items from /News section var news = DocumentHelper.GetDocuments("CMS.News") .OnSite("CorporateSite") .Path("/News", PathTypeEnum.Children) .Culture("en-us", "cs-cz");


Or, make sure that if there is a requested culture version missing, the site’s default culture version of a document will be included in the results instead:

// Get Czech language version of news items from /News section. If not available get English version (default). var news = DocumentHelper.GetDocuments("CMS.News") .OnSite("CorporateSite") .Path("/News", PathTypeEnum.Children) .Culture("cs-cz") .CombineWithDefaultCulture();


This is a significant performance improvement in comparison with the current API since the combination is evaluated right on the SQL side and not in an application code – less data is pulled from database.

Nesting level

You can specify which relative level you want the documents to reach within a specified section. The code below only gets documents nested directly under the site root:

// Get documents nested directly under a root of the site var documents = DocumentHelper.GetDocuments("CMS.MenuItem") .OnSite("CorporateSite") .Path("/", PathTypeEnum.Children) .NestingLevel(1);


Please note that you don’t specify the absolute nesting level of a document, but instead one that is relative to the document specified by the Path method. The direct child documents always have level 1 nesting despite the level of the parent document.

Related documents

If you have relationships defined between documents, you can easily parameterize the query to find any related documents:

// Simulates GUID of some document var nodeGuid = Guid.NewGuid(); // Get documents related to specified document var documents = DocumentHelper.GetDocuments("CMS.MenuItem") .OnSite("CorporateSite") .InRelationWith(nodeGuid, "IsRelatedTo");


The relationship name parameter is optional. If not provided, the documents are not limited to any specific relationship. There is also an additional optional parameter used to designate which side of the relation is the document on – either left, right or both. The default value is both sides.

Document categories

Documents in a category can be easily selected by defining the category name:

// Get documents from Development category var documents = DocumentHelper.GetDocuments("CMS.MenuItem") .OnSite("CorporateSite") .InCategory("Development");


By default, the name defines the site category and the data is limited only if the category is enabled. This behavior can be changed using the optional parameters of this method.

Filtering duplicate documents

Sometimes when using linked documents, it’s handy to get documents from a section without duplicates – if both the original and the linked document are available, only the original will be included.

// Get all site documents without linked documents var documents = DocumentHelper.GetDocuments("CMS.MenuItem") .OnSite("CorporateSite") .FilterDuplicates();


When using this method you don’t need to check the results for the duplicities.

Checking permissions

The query supports the filtering of data based on given user permissions. This support is available for the current API as well, but is more robust for the query. In the current API, you need to get the documents and then filter the data, so obtaining a specific number of documents is not exactly a straightforward procedure because you’ll never know how many of them will be filtered due to the permissions check. This is completely taken care of in the query implementation, along with the more effective data handling. By default, the context of the current user is used. You can provide context for a different user by applying the CMSActionContext:

// Get all news items which are available for public user var user = UserInfoProvider.GetUserInfo("public"); using (new CMSActionContext(user)) { var news = DocumentHelper.GetDocuments("CMS.News") .OnSite("CorporateSite") .Path("/News", PathTypeEnum.Children) .CheckPermissions(); }


The same applies when using paging together with the permissions check.

Paging support

The current documents API is inefficient when paging is requested because all of the documents up to the required page are pulled from the database and then filtered in the application.

The query brings full support of data paging to work with data efficiently. All viewer web parts for documents in version 8 were optimized to use query which leads to better performance when displaying documents on a live site.

Please note: To leverage the full support of data paging in web parts, you need to connect the Universal pager web part to the viewer web part.

Requesting a specific page for a set of documents is quite straightforward:

// Get second page (of size 2) of news items from /News section sorted by title var news = DocumentHelper.GetDocuments("CMS.News") .OnSite("CorporateSite") .Path("/News", PathTypeEnum.Children) .OrderBy("NewsTitle") .Page(1, 2);


Together with the Columns method, you can significantly reduce the amount of data pulled from the database and increase the performance.

Multiple document types data

In the previous code examples you can see how to work with documents of a single document type.

In some scenarios, you need to limit the set of document types and only work with general data. In this case you can simply define the list of types:

// Get all news items and articles from site var documents = DocumentHelper.GetDocuments() .Types("CMS.News", "CMS.SimpleArticle") .OnSite("CorporateSite");


In other scenarios, you need to work with a specific data type. With the MultiDocumentQuery, you can get documents of different types all at once by specifying either local parameters for each type, or global parameters for the whole query.

Below, you can see how to get documents of several types with all of the specific data parameterized by a global condition:

// Get all news items and articles from site containing 'Kentico' in name var documents = DocumentHelper.GetDocuments() .Type("CMS.News", q => q.Default()) .Type("CMS.SimpleArticle", q => q.Default()) .OnSite("CorporateSite") .WhereLike("DocumentName", "%Kentico%");


Using the Default method for type-specific query ensures its default state – query is parameterized only based on related system settings (Combine with default culture etc.).

You can define conditions and other parameters for each type as well:

// Get all news items and articles from site containing 'Kentico' in names var documents = DocumentHelper.GetDocuments() .Type("CMS.News", q => q.WhereLike("NewsTitle", "%Kentico%")) .Type("CMS.SimpleArticle", q => q.WhereLike("ArticleTitle", "%Kentico%")) .OnSite("CorporateSite");


The local condition is related to the type-specific query, therefore you can use type-specific columns when parameterizing the query. This doesn’t apply to the global parameters. In this case, only general document columns can be used.

You can even combine local and global parameters:

// 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");


By default, the MultiDocumentQuery selects type-specific columns (coupled columns) only if the type query is parameterized (example above). If you want to include the coupled columns in the results and you don’t need to parameterize the type query, simply use:

// Get news items from /News section including type specific columns var documents = DocumentHelper.GetDocuments() .Type("CMS.News") .Path("/News", PathTypeEnum.Children) .OnSite("CorporateSite") .WithCoupledColumns();


Or, include only coupled columns:

// Get news title and all article specific columns, but no general document columns var documents = DocumentHelper.GetDocuments() .Type("CMS.News", q => q.Columns("NewsTitle") ) .Type("CMS.SimpleArticle") .OnSite("CorporateSite") .OnlyCoupledColumns();


For the MultiDocumentQuery you can use all of the methods available for the DocumentQuery to parameterize it – conditions, permissions checking, paging etc. The final query generated by the multi-query can be quite complicated. You can simply get the query text by calling the ToString method of the query instance.

How to work with data

The query provides two ways in which you can work with the data returned by the query.

Items enumeration

You can easily use the query to iterate through the collection of returned TreeNode instances and access its properties:

// Get all news items and articles from site var documents = DocumentHelper.GetDocuments() .Types("CMS.News", "CMS.SimpleArticle") .OnSite("CorporateSite") .OrderBy("DocumentName"); // Go through the documents foreach (var document in documents) { Response.Write(HTMLHelper.HTMLEncode(document.DocumentName) + "<br />"); }

DataSet rows iteration

If you need to work with results as a DataSet data type, whether your customization code is from older versions, or if you’re simply used to this approach, there is still a way to achieve it. There is one other difference to take notice of when working with several document types at once: The API known from previous versions returned a DataSet with a table per document type, so you needed to iterate through the tables and their rows. The query joins all of the type-specific columns into a single table together with a system column identifying the document type. Here is an example of how you can read the data:

// Get all news items and articles from site var documentsDs = DocumentHelper.GetDocuments() .Types("CMS.News", "CMS.SimpleArticle") .OnSite("CorporateSite") .OrderBy("DocumentName") .Result; // Or you can use TypedResult // Go through the rows foreach (var row in documentsDs.Tables[0].Rows) { var className = TreeNodeProvider.GetClassName((string)row[SystemColumns.SOURCE_TYPE]); var documentName = (string)row["DocumentName"]; Response.Write(HTMLHelper.HTMLEncode(documentName) + "<br />"); }


In the code example, you can see how you can easily get the className representing the document type and the document data. Use the Result property to get the DataSet object from the query or the TypedResult property for strongly typed results (InfoDataSet type).

Future plans

In Kentico 8 we wanted to provide you with at least the same set of options that exist for the current documents API. We know that there could be many more improvements and simplifications done to the API calls, and we definitely will continue improving the DocumentQuery and MultiDocumentQuery. Together with unifying the API for managing published and latest versions of documents in the future, we believe that we will provide an easy to use and powerful API.

Please let us know what you are missing in the DocumentQuery or MultiDocumentQuery via comments below or by creating a uservoice idea. We appreciate all of your feedback.

Thank you!

Share this article on   LinkedIn Google+

Jaroslav Kordula

Jaroslav joined Kentico in 2006. He is a Technical Leader in a development team whose main focus is content management. This includes Documents, Custom tables, Media libraries and other related functionality.

Comments

Jaroslav Kordula commented on

Hi Uma,
if you work with one page type, the best way is to use the DocumentHelper.GetDocuments("custom.XY_SharedContent") method which pulls the coupled data as well by default. The DocumentHelper.GetDocuments() method is intended to be used especially when you work with multiple types at once. Anyway you can use the .WithCoupledColumns() method to include the coupled columns in your code snippet. Moreover if you need only the coupled data, .OnlyCoupledColumns() method can be used to limit the amout of data pulled from the database.

Jaroslav

Jaroslav Kordula commented on

Hi Maciek,
thank you for your comment. The Parent property of a TreeNode doesn't contain the coupled data (Image field). There is no easy way how to pull the coupled data additionally for the instance. From my point of view I would create a separate view model and handle the parents by my own, because with your approach there will be an extra bunch of database query calls for each item to get the parent.

Jaroslav

Uma S. commented on

We have defined Page Types and have documents for that page type. While using MultipleQuery and retrieving the TreeNode it has the custom column names but GetValue method fails.

Custom Page Type is defined with code name- custom.XY_SharedContent. This type has two fields: Key and Value.

Following code retrieves tree nodes of type custom.XY_SharedContent but the custom fields are null.
var docs = DocumentHelper.GetDocuments().Type("custom.XY_SharedContent");

Any thoughts on how to retrieve custom fields?

Maciek Andruszko commented on

I have an issue with getting Parents/Children 's custom fields from items strongly typed using you API. For example I have 2 documents like:"CarItem" and "CarBrand". I would like to gather from the Db all cars, but for each car I would like to display the image of it's parent brand. I can easily get cars by:
IEnumerable<CarItem> cars = DocumentHelper.GetDocuments<CarItem>().OnSite("CAR").Published();
Then in my View(I use MVC development approach) I would like to show list of cars with proper Image.

@model IEnumerable<CMS.DocumentEngine.Types.CarItem>
@foreach(item in Model){
<image src="/CMSPages/GetFile.aspx?guid=@item.Parent.Image"/> -> That's not working .
}
if I try to use GetValue("Image") (@item.Parent.GetValue("Image")) -> its also gives me null as a result.

What is the best approach to handle it. It can be bypassed by creating a custom class which will contain all the necessary information and pass it to the View, but that's not a convenient solution in my opinion. Can I handle it in an easier way ?

Jaroslav Kordula commented on

Hi Victor,

thanks for the comment. The DocumentHelper.GetDocuments() method is designed to work with multiple page types at once. By default it pulls only general data from the database. You need to parametrize the query by calling the WithCoupledColumns() method to include the custom data. Anyway I suggest you to use the DocumentHelper.GetDocuments(string className) method which is designed to work with exactly one page type. It automatically includes the custom data.

Jaroslav

Victor H Garcia commented on

Awesome API. However I do have an issue when trying to retrieve specified columns from a custom field into the page type.

For example I made a custom page type that of course contains some fields and I would like to retrieve only those specific columns, so what I'm trying to do is:

var documents = DocumentHelper.GetDocuments()
.Type(className, q=> q.Columns("CustomItemName", "CustomItemAddress"))
.OnSite("demo")
.ToList();

It returns the full list of items, even the .count is just fine. however the custom data is not returned.

any ideas of why the custom fields data are not returned?