Getting Orchard content items out of HQL
I the two previous posts (here and here), I’ve showed how to build HQL queries against the Orchard database. Once you’ve built the query, you’ll want to get results, often in the form of fully-built content items. In lots of cases, you’ll want to paginate the results, for which you’ll need a total count, and detailed results for only the current page. This post will show you how to do all these things.
Let’s start by creating two queries: one for the count, and one for the current page. Both queries have a lot in common, so it makes sense to break them down into smaller parts:
const string fromTables =
"FROM Orchard.ContentManagement.Records.ContentItemVersionRecord ItemVersion"
+ " JOIN ItemVersion.ContentItemRecord Item"
+ " JOIN Item.UserPartRecord User"
+ " WHERE ItemVersion.Published = true"
+ " AND User.UserName IS NOT NULL";
const whereClause =
"User.Id NOT IN (SELECT Role.UserId FROM Orchard.Roles.Models.UserRolesPartRecord Role)";
const orderBy = "ORDER BY User.UserName";
The number of items does not depend on their order, so we can build the count query to be a little simpler:
var countQuery = session.CreateQuery(
"SELECT COUNT(User.Id) "
+ fromTables
+ " AND " + whereClause);
Then the full query is:
var pageQuery = session.CreateQuery(
"SELECT DISTINCT User.Id, User.UserName "
+ fromTables
+ " AND " + whereClause
+ " " + orderBy)
.SetFirstResult(pager.GetStartIndex())
.SetMaxResults(takeNum);
We’re specifying the user name as a column in the results here because we use it in the order by clause, but we’re not going to use it otherwise. What we’re really interested in is the id. Notice the use of SetFirstResult and SetMaxResults to inform nHibernate that we only want one page of data.
We could now send those two queries all at once by bundling them into a multiple query, if that was supported by SqlCe:
var resultSets = session
.CreateMultiQuery()
.Add(countQuery)
.Add(pageQuery)
.SetCacheable(true)
.List();
Unfortunately, it's not, so we need to send the queries one by one...
We can get the count like this:
var totalCount = countQuery.List<long>().First();
The list of ids can then be extracted this way:
var ids = pageQuery.List<int>();
Once we have the list of ids, we can get the full content items, in the same order, from the content manager:
var results = contentManager
.GetMany<UserPart>(ids, VersionOptions.AllVersions, QueryHints.Empty);
If you know that you’ll want to access more than just the user part later, make sure that you include the corresponding query hints into this call.
At this point, you’re probably wondering why we’re making a second query instead of getting all the data necessary to build the content items at once in our HQL query. Well, we could, although that would not be easy: we would have to reproduce a lot of the work that the content manager is doing in Get and GetMany. In particular, there are lots of optimizations and potential for entity caching in GetMany. In the end, I don’t think it’s worth the trouble. In fact, this is exactly how projections are handling their results as well. Where do you think I learned all this from? ;)
UPDATE: It turns out that Sql CE does not support count(distinct), and nHibernate does not support nested queries in FROM clauses. The workaround is to remove the DISTINCT keyword, and use a GROUP BY if necessary.
UPDATE 2: it turns out that SqlCE does not support multiple queries either. I'm tempted to advise to stop using this silly excuse for a database, but I realize that it's kind of convenient in a development environment, so I'm fixing the post accordingly.