Retrieving random content items (rows) from a SQL database in Orchard with HQL queries

by Oliver 22. February 2014 12:37

We're adding some Premium functionality to discoverize right now, and part of that is the so-called Premium block which is a showcase of six Premium entries. Now, choosing the right entries for that block is the interesting part: as long as we don't have six Premium entries to show, we want to fill up the left over space with some random entries that haven't booked our Premium feature, yet.

Get random rows from SQL database

There are plenty of articles and stackoverflow discussions on the topic of how to (quickly) retrieve some random rows from a SQL database. I wanted to get something to work simply and quickly, not necessarily high performance. Incorporating any kind of hand-crafted SQL query was really the last option since it would mean to get hold of an ISessionLocator instance to get at the underlying NHibernate ISession to then create a custom SQL query and execute it. Not my favorite path, really. Luckily, the IContentManager interface contains the method HqlQuery which returns an IHqlQuery containing these interesting details:

/// <summary>
/// Adds a join to a specific relationship.
/// </summary>
/// <param name="alias">An expression pointing to the joined relationship.</param>
/// <param name="order">An order expression.</param>
IHqlQuery OrderBy(Action<IAliasFactory> alias, Action<IHqlSortFactory> order);

…and IHqlSortFactory contains a Random() method. This finally got me going!

HQL queries in Orchard

HQL queries are a great feature in (N)Hibernate that allow you to write almost-SQL queries against your domain models. I won't go into further detail here, but be sure to digest that!

Orchard's IContentManager interface contains the method HqlQuery() to generate a new HQL query. Unfortunately, there's almost no usage of this feature throughout the whole Orchard solution. So let me document here how I used the HqlQuery to retrieve some random entries from our DB:

// retrieve count items of type "Entry" sorted randomly
return contentManager.HqlQuery()
    .ForType("Entry")
    .OrderBy(alias => alias.ContentItem(), sort => sort.Random())
    .Slice(0, count)
    .Select(item => item.Id);

And one more:

// retrieve <count> older items filtered by some restrictions, sorted randomly
return contentManager.HqlQuery()
    .ForPart<PremiumPart>()
    .Where(alias => alias.ContentPartRecord<PremiumPartRecord>(),
           expr => expr.Eq("Active", true))
    .Where(alias => alias.ContentPartRecord<PremiumPartRecord>(),
           expr => expr.Lt("BookingDateTime", recentDateTime))
    .OrderBy(alias => alias.ContentItem(), sort => sort.Random())
    .Slice(0, count)
    .Select(item => item.Id);

Even with the source code at hand, thanks to Orchard's MIT license, the implementation of this API in the over 600 lines long DefaultHqlQuery is not always straight-forward to put into practice. Most of all I was missing a unit test suite that would show off some of the core features of this API and I'm honestly scratching my head of how someone could build such an API without unit tests!

Random() uses newid() : monitor the query performance

The above solution was easy enough to implement once I've got my head around Orchard's HQL query API. But be aware that this method uses the newid() approach (more here) and thus needs to a) generate a new id for each row in the given table and b) sort all of those ids to then retrieve the top N rows. Orchard has this detail neatly abstracted away in the ISqlStatementProvider implementation classes. Here's the relevant code from SqlServerStatementProvider (identical code is used for SqlCe):

public string GetStatement(string command) {
    switch (command) {
        case "random":
            return "newid()";
    }
    return null;
}

For completeness, here's the generated SQL from the first query above (with variable names shortened for better readability):

select content.Id as col_0_0_
from Test_ContentItemVersionRecord content
    inner join Test_ContentItemRecord itemRec
        on content.ContentItemRecord_id = itemRec.Id
    inner join Test_ContentTypeRecord typeRec
        on itemRec.ContentType_id = typeRec.Id
where ( typeRec.Name in ('Entry') )
    and content.Published = 1 order by newid()
OFFSET 0 ROWS FETCH NEXT 3 ROWS ONLY

This approach works well enough on small data sets but may become a problem if your data grows. So please keep a constant eye on all your random queries' performance.

Happy HQL-ing!

Add comment

  Country flag

biuquote
  • Comment
  • Preview
Loading

About Oliver

shades-of-orange.com code blog logo I build web applications using ASP.NET and have a passion for javascript. Enjoy MVC and Orchard CMS, and I do TDD whenever I can. I like clean code. Love to spend time with my wife and our three children. My profile on Stack Exchange, a network of free, community-driven Q&A sites

About Anton

shades-of-orange.com code blog logo I'm a software developer at teamaton. I code in C# and work with MVC, Orchard, SpecFlow, Coypu and NHibernate. I enjoy beach volleyball, board games and Coke.