Sorting blogs by latest entres (3.1)

Mattias Våglin
Member since: 2008
 

Hi,

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.

/Mattias

#28930 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
    AS
    BEGIN

    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)
    SELECT * FROM blogs
    WHERE intRow BETWEEN @intStartRow AND @intEndRow


    END

     

    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.

     

    #28935 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()
    {
    return
    "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 :)

    #28936 Edited, Mar 29, 2009 17:00
  • tost
    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,
    Tom

    #28939 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?

    #28940 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.

     

    /Mattias

    #28944 Mar 30, 2009 11:43
  • tost
    Member since: 2007
     

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

     //Tom

    #28952 Mar 30, 2009 16:18