Hi,
I have a paging method that uses the criteria API's SetMaxResults and
SetFirstResult. Functionally, it works fine.
What I'd like to do now is provide another method that retrieves the
index of a given item within the set of all items, thus allowing me to
calculate what page that item is in.
I am able to achieve this with the following SQL:
SELECT TOP 1
ROW_NUMBER() OVER
(
ORDER BY PublicationTimestamp -- this is the order
the user has
sorted by
) AS [Index]
FROM
Articles
ORDER BY -- this
is an order to get the relevant article at the
top of the list and permit the top 1 clause
CASE
WHEN Id = 8604 THEN 0 -- this would
be set to the ID of the
article whose index is to be determined
ELSE 1
END
However, I'm having trouble translating this into either HQL or the
criteria API. Is there an accepted way of achieving this with the
criteria API or with HQL?
For reference, my paging method looks like this:
var multiCriteria = session.CreateMultiCriteria();
// this criteria finds all distinct article IDs that match any
supplied filter
var idsDetachedCriteria = DetachedCriteria.For<Article>()
.SetProjection(Projections.Distinct(Projections.Property
("Id")));
// this criteria counts the total number of articles that match
the filter
var countQuery = session.CreateCriteria<Article>()
.SetProjection(Projections.Count("Id"))
.Add(Subqueries.PropertyIn("Id", idsDetachedCriteria));
// this criteria gets a specific page of articles that match the
filter
var listQuery = session.CreateCriteria<Article>()
.Add(Subqueries.PropertyIn("Id", idsDetachedCriteria))
.SetMaxResults(maximumResults)
.SetFirstResult(resultPage * maximumResults)
.AddOrder(new Order(GetPropertyForSortBy(sortBy), sortOrder ==
SortOrder.Ascending));
if (filter != null)
{
// this applies the filter to the IDs detached criteria
var criteriaNodeVisitor = new CriteriaNodeVisitor
(idsDetachedCriteria);
filter.Expression.Visit(criteriaNodeVisitor);
}
multiCriteria.Add(countQuery);
multiCriteria.Add(listQuery);
var results = multiCriteria.List();
return new ArticleListPage((int)((IList)results[0])[0], ((IList)
results[1]).Cast<Article>());
Thanks,
Kent
--
You received this message because you are subscribed to the Google Groups
"nhusers" group.
To post to this group, send email to [email protected].
To unsubscribe from this group, send email to
[email protected].
For more options, visit this group at
http://groups.google.com/group/nhusers?hl=en.
