Database changes in Kentico 9 – How to deal with missing views

Martin Florian — Jan 22, 2016

As you may have noticed, one of the changes in Kentico version 9 is that all page-related views and the View_COM_SKU views were removed from the database with the exception of View_CMS_Tree_Joined, which is used as the base for the querying of page data. This change may have an impact on you if you are using these views in areas such as your custom queries. In this article, we will take a look at how to rewrite such a query to work with version 9.

Let’s imagine the following scenario:

You were using the following query in version 8 to get the top 10 best rated, published articles from a Corporate Site:

SELECT TOP 10 * FROM View_CONTENT_Article_Joined

             WHERE Published = 1 and SiteName = 'CorporateSite' ORDER BY DocumentRatingValue DESC

There are a couple of things that have to be replaced in this query if you want to use it in version 9. Let’s start with the Page Type view.

The best way to replace the view in the query is to find out how the view was created in version 8.2 in the first place. If you go to SQL Server Management Studio and expand the Views in your Kentico database, you can right-click the View_CONTENT_Article_Joined and select Script View as > CREATE To > New Query Editor Window. Here you can see the query. You are interested in the following line which selects the data for the view:

SELECT View_CMS_Tree_Joined.*, CONTENT_Article.* FROM View_CMS_Tree_Joined INNER JOIN CONTENT_Article ON View_CMS_Tree_Joined.DocumentForeignKeyValue = CONTENT_Article.[ArticleID] WHERE (ClassName = 'CMS.Article')

So we can update our original query to look like this:

SELECT TOP 10 * 
FROM 
(
	SELECT * 
	-- Page type view
	FROM View_CMS_Tree_Joined INNER JOIN CONTENT_Article ON View_CMS_Tree_Joined.DocumentForeignKeyValue = CONTENT_Article.ArticleID
	WHERE (ClassName = 'CMS.Article')
) AS view_replacement
WHERE Published = 1 and SiteName = 'CorporateSite' 
ORDER BY DocumentRatingValue DESC

Now, we have to remove the usage of the Published and SiteName columns, which were also removed in version 9. So the final query should look like this:

SELECT TOP 10 * 
FROM 
(
	SELECT * 
	-- Page type view
	FROM View_CMS_Tree_Joined INNER JOIN CONTENT_Article ON View_CMS_Tree_Joined.DocumentForeignKeyValue = CONTENT_Article.ArticleID
	WHERE (ClassName = 'CMS.Article')
) AS view_replacement
WHERE
-- Published 
([DocumentCanBePublished] = 1 AND ([DocumentPublishFrom] IS NULL OR [DocumentPublishFrom] <= GETDATE()) AND ([DocumentPublishTo] IS NULL OR [DocumentPublishTo] >= GETDATE()))
AND 
-- SiteName
NodeSiteID = (SELECT SiteID FROM CMS_Site WHERE SiteName = 'CorporateSite')
ORDER BY DocumentRatingValue DESC

You might also want to be able to get SKU data. If you look at the script creating View_CMS_Tree_Joined in version 8, you’ll see the following join in the SELECT statement:

LEFT OUTER JOIN dbo.View_COM_SKU AS SKU ON V.NodeSKUID = SKU.SKUID

This is what you have to incorporate in your query to be able to use SKU data:

SELECT TOP 10 * 
FROM 
(
	SELECT * 
	-- Page type view
	FROM View_CMS_Tree_Joined INNER JOIN CONTENT_Article ON View_CMS_Tree_Joined.DocumentForeignKeyValue = CONTENT_Article.ArticleID
	LEFT OUTER JOIN COM_SKU ON NodeSKUID = SKUID
	WHERE (ClassName = 'CMS.Article')
) AS view_replacement
WHERE
-- Published 
([DocumentCanBePublished] = 1 AND ([DocumentPublishFrom] IS NULL OR [DocumentPublishFrom] <= GETDATE()) AND ([DocumentPublishTo] IS NULL OR [DocumentPublishTo] >= GETDATE()))
AND 
-- SiteName
NodeSiteID = (SELECT SiteID FROM CMS_Site WHERE SiteName = 'CorporateSite')
ORDER BY DocumentRatingValue DESC

Rewriting your queries like this is the preferred and best practice, but if you were using views in multiple queries, this might be a little time consuming. For a quick fix, you can recreate the Views you need in the database, but we recommend using this only as a temporary solution.

If you are calling your custom queries from API, the recommended way here is to use Document Query API instead. The drawback in version 8 was that you couldn’t JOIN queries. This is another improvement in version 9, we have added support of DocumentQuery [JK1] [MF2] joins. Here is an example of joining the user data based on DocumentModifiedByUserID:

Let’s say you were using the same query as in the previous example, but wanted to get the full name of the user that last modified the article:

select top 10 View_CONTENT_Article_Joined.*, FullName  
from View_CONTENT_Article_Joined 
inner join CMS_User ON DocumentModifiedByUserID = UserID
where Published = 1 and SiteName = 'CorporateSite' 
ORDER by DocumentRatingValue DESC

The document query could look like this:

        var docs = DocumentHelper
            .GetDocuments("CMS.Article")
            .OnSite("CorporateSite")
            .TopN(10)
            .Published()
            .OrderByDescending("DocumentRatingValue")
            .Source(sourceItem =>
    sourceItem.LeftJoin<UserInfo>("V.DocumentModifiedByUserID", "UserID"));

Share this article on Twitter Facebook LinkedIn