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.

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 :)

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

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?

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

/Mattias

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

 //Tom