We found a nice little trick to pull content at random from Kentico using a standard pages data source.
Every once in a while, we all need to do something random, and sometimes it’s even related to work. Something we came across here at Ridgeway was a requirement to pick out random documents from a collection to display to the end user. We found a nice little trick to pull content at random from Kentico using a standard pages data source.
Why would you want to do that?
I have to admit, there aren’t a great many occasions where we’ve needed to do this. But the need does arise. Some sample scenarios include:
- Picking five partner logos to show on your footer
- Selecting random customer reviews for your product page (5-star of course!)
- Selecting random articles or products perhaps?
The solution itself is quite simple, though it does need some care and attention where caching is concerned. We’ll need to make some changes to a web part which isn’t really considered best practice in my opinion. Then again, there is a reason that it’s called ‘best practice’ rather than ‘the only practice’.
How to do it
The following steps assume that you have a blank Kentico solution.
- Open up the Pages application, and in the root of your site create a folder named Quotes.
- Inside this newly created folder, add five or more Page (menu item) pages using the Empty template and give them some random quotes (i.e. Simpson’s quotes) for document names.
- Now, back in the root of your site, create a new Page (menu item) with an ad-hoc template and call it Random documents.
- In the new page’s Design tab, add a Repeater Web Part and configure it as follows:
||Page (menu item)
|Select top N pages
|Maximum nesting level
||Page (menu item) > List item
That's the first part done. If you either load the live page in preview mode or flip to the Page tab, you should see a list of three random quotes. What you will notice is, this is cached -of course it is, that's the out of the box behaviour and it's good. We want to be bad, however, and remove/disable this cache.
There are a number of ways to achieve this, but the method with least impact is just to disable the cache on the Web Part. To do this, in the Web Part settings, scroll down to System settings and change the Cache minutes to 0. This tells Kentico that we don’t want to cache the content for this control.
Now that you have done this - each time you reload your page, you’ll get a different set of results.
Why does it work?
Under the hood, this is actually quite simple and makes use of an inbuilt SQL function, NEWID(). NEWID() is a SQL function that basically generates a new Guid each time it is called, and given that Guids are nice and random, when we sort a set of results by them, we get a different order each time.
If you enable SQL debugging in Kentico, you can see the query that we’re running to get our random quotes.
DECLARE @NodeSiteID int = 1;
DECLARE @Now datetime2 = '9/17/2016 4:07:28 PM';
DECLARE @NodeAliasPath nvarchar(max) = N'/Quotes/%';
DECLARE @DocumentCulture nvarchar(max) = N'en-US';
SELECT TOP 3 [MenuItemName], [DocumentCulture], [NodeID], [NodeLinkedNodeID], [NodeSiteID], [ClassName], [NodeLevel], [NodeOrder], [NodeParentID]
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 (([DocumentCanBePublished] = 1 AND ([DocumentPublishFrom] IS NULL OR [DocumentPublishFrom] <= @Now) AND ([DocumentPublishTo] IS NULL OR [DocumentPublishTo] >= @Now)) AND [NodeAliasPath] LIKE @NodeAliasPath AND [DocumentCulture] = @DocumentCulture)
ORDER BY NEWID()
So, now we have a way of selecting random documents in Kentico using the out-of-the-box features available to Portal developers. This kind of capability makes Kentico’s Portal engine a powerful proposition in my mind. It gives us, as developers, the ability to quickly implement features for our customers on the site without having to jump into the back-end, write custom web parts, and deploy code to the server.
There are two take-away points here:
- By looking at the SQL debugging, you can see what Kentico is doing and how it queries the data. You can use this knowledge to fine-tune what you’re doing from a performance point of view.
- It’s not always obvious that you can add your own SQL to the data source web parts. If you have unusual requirements, try to bear this in mind and see what you can achieve in SQL. If you need to, you can always create a custom query to get the exact data that you need.