Sorting blogs by latest entres (3.1)

Mattias Våglin
Member since: 2008


We have a site that contains a huge amount of blogs. We want to be able to display all the blogs on that site in the order they were last updated (ie, when the latest entry was created), but we're drawing completely blank.

Or not completely, I've written code that gets the last x blogs based on the latest entries, but if we're going to list hundreds of blogs in the same way, performance will be awful.

I can't find any support for this in EPiServer Community, so any ideas would be valuable.

 I wish I could just add an attribute to the blog and sort them based on that, but I can't find a way to sort on attributes (thorugh queries) either.


Mar 27, 2009 15:59

Hi Mattias!

If you can't find a way to use the query system to do this (please let me know if you do) and the number of blogs is big (which would elliminate in-memory sorting) I would suggest you create your own stored procedure for this. It should look something like this:

CREATE PROCEDURE spGetBlogsOrderedByLastEntryDate
@intPage int,
@intPageSize int

DECLARE @intStartRow int;
DECLARE @intEndRow int;
SET @intStartRow = (@intPage -1) * @intPageSize + 1;
SET @intEndRow = @intPage * @intPageSize;

WITH blogs AS
(SELECT b.intID, b.intSiteID, b.strBlogName, b.blnActive, b.intNumEntries,
b.datTimeStamp, b.intImageGalleryID, b.intPresentationImageID,
b.strPresentationText, b.intAuthorID,
ROW_NUMBER() OVER(ORDER BY MAX(e.datTimeStamp) DESC) as intRow,
COUNT(b.intID) OVER() AS intTotalHits
FROM tblEPiServerCommunityBlog b
LEFT JOIN tblEPiServerCommunityBlogEntry e ON e.intBlogID = b.intID
GROUP BY b.intID, b.intSiteID, b.strBlogName, b.blnActive, b.intNumEntries,
b.datTimeStamp, b.intImageGalleryID, b.intPresentationImageID,
b.strPresentationText, b.intAuthorID)
WHERE intRow BETWEEN @intStartRow AND @intEndRow



Execute the SP and retrieve the result using a DataReader. The DatabaseHandler class, located in StarSuite .Core.Data or EPiServer.Common.Data depending on version has a method named GetReader which can assist you with that. 

Once you have the reader, locate the entity provider for Blog, create a new BlogCollection or List<Blog>, loop through the reader and create new instances of Blog using the entity provider and add them to the blog collection.

This should look something like this:

List blogs = new List();
object[] storedProcedureParameters = new object[2];
storedProcedureParameters[0] = 1; //Replace with actual page number
storedProcedureParameters[1] = 10; //Replace with actual page size
using (DbDataReader reader = DatabaseHandler.GetReader(
"spGetBlogsOrderedByLastEntryDate", storedProcedureParameters))
IEntityProvider blogProvider = EntityProviderHandler.GetEntityProvider(typeof (Blog));
while (reader.Read())
blogs.Add(blogProvider.GetEntityInstance(typeof(Blog), reader));


You will probably want to cache the result using CacheHandler.GetCachedObject (the overloads that accepts a delegate) and also put this code in a separate class library or atleast a utility class.

You should also create module (either a HTTPModule or a IModule with a module dependency on the blog module) in which you clear the cache each time BlogHandler.EntryAdded or BlogHandler.EntryRemoved is fired.

Let me know if you need more example code and I'll see what I can do.

Also, note that this solution, while good performance wise is not optimal in the sense that it relies on the community frameworks internal database tables which may change during upgrades. Given the amount of blogs and blog entries that can exists in a community and how important performance can be in a community I would live with it though, especially if this feature is important for the site. If it however is just a nice to have feature I would recommend adding a feature request to the EPiServer Community development team and holding off on developing it.


Edited, Mar 29, 2009 13:48

On second thought, you can also create you own query class that inherits from BlogQuery:

internal class BlogsByLatestEntryQuery : BlogQuery
public override string GetQuery()
"SELECT blog FROM EPiServer.Community.Blog.Blog AS blog"
+ " LEFT JOIN blog.Entries AS entry ORDER BY MAX(entry.Created) DESC"
+ " GROUP BY blog.ID, blog.Created, blog.Name, blog.ImageGallery, "
+ " blog.Active, blog.NumEntries, blog.PresentationImage,"
+ " blog.PresentationText, blog.Active, blog.Author, blog.Site";


Use it this way (note that any additional criterias you might add wont have any effect):

BlogQuery blogQuery = new BlogsByLatestEntryQuery();
BlogCollection blogs = BlogHandler.GetQueryResult(blogQuery, 1, 10);


This solution, while not exactly pretty, wont be dependent on the community framworks database structure and you will also get caching. You will however still need to clear the cache when a new blog is created.

Also, keep the query class' visibility to an absolute minimum as it violates a bunch of design principles :)

Edited, Mar 29, 2009 17:00
Member since: 2007

Nice solution Joel!

Regarding the cache, in this case I would recommend using an explicit timeout, since I don't think this listing always have to be up to date.

Best regards,

Mar 30, 2009 9:35

Thanks Tom!

I was trying to build the query using custom criterions but gave up after a while. Do you see any, not overly complex, way of doing that?

Edited, Mar 30, 2009 9:43
Mattias Våglin
Member since: 2008

Thanks a lot. I'll give it a try and report my progress here.



Mar 30, 2009 11:43
Member since: 2007

Joel - No, I'm sorry to say I don't see an easy way of doing this...


Mar 30, 2009 16:18
This thread is locked and should be used for reference only. Please use the Legacy add-ons forum to open new discussions.