umbraco/Umbraco-CMS — pull request #448 by AndyButland (2014-08-14 06:55:27+00:00)
Title: U4-5003 - Moving paging for list view to database

Currently the paging implemented on the list view is carried out in memory - so all records are retrieved from the database which is a relatively expensive operation (looks like an N+1 query operation is required. As U4-5003 illustrates, this can be quite slow if the number of child content items is large, which it could well be in scenarios were the list view is being utilised.
http://issues.umbraco.org/issue/U4-5003

This PR implements the paging at the database level.

I've submitted another PR (#435) which is on configuration of the list view - this one is related but not dependent on it, so you should be able to accept or otherwise either at any appropriate time.

What I've done looks to work OK and is supported by the unit tests, but there are a few things for the reviewer to note - around how it was implemented and to make some possible improvements.

- Have created a new method in ContentRepository called **GetPagedResultsByQuery()**, based on the existing PerformGetQuery.

I've simplified the base query in this new method to:

var sqlClause = GetBaseQuery(false);
var translator = new SqlTranslator(sqlClause, query);
var sql = translator.Translate()
.Where(x => x.Newest);

As opposed to this in PerformGetByQuery:

var sqlClause = GetBaseQuery(false);
var translator = new SqlTranslator(sqlClause, query);
var sql = translator.Translate()
.Where(x => x.Newest)
.OrderByDescending(x => x.VersionDate)
.OrderBy(x => x.SortOrder);

But seems OK given we only want the document Ids, ordered as the user has requested.

- Ordering by updating user is using the Id not the name - which is not going to work of course, as whilst it will group the user names, it won't order them alphabetically. This would need more significant work to pull in the name of the user both in the database query and in the list of IContent returned, as both need to be ordered (for the database paging, and then once the full content objects are retrieved).

- Ordering is based on a field name derived from ContentItemBasic, which is the base type of the list of content that is currently paged and ordered in memory in ContentController. The name of that field is not the same as either the database field or the equivalent IContent field (where available) so I have some switch statements in ContentRespository to convert to the right one. If this PR, and the related one around list configuration (#435), is accepted, we could look to change this to use one of the two fields we actually need to sort on as the key - and hence remove at least one of these conversion switch statements.

- I made one small addition to PetaPoco.cs. Not sure if this had already been amended and hence that's OK, or whether you are keeping this untouched in case you want to upgrade it. If so this additional function OrderByDescending should probably be moved to an extension method.

In addition to the paging amends I've made a couple of minor other fixes, which you could pull in from the particular commits if you wanted them separately

1. I noticed that if you filter on the list view and get no results, the filter box disapears so you can't clear your filter and get the results back. I've changed the elements that are shown and hidden to fix this in commit #096e10a.

2. Also noticed if you use the filter the form $dirty flag is checked, which puts up the "you have unsaved changes" prompt when of course you don't. I've added a directive to prevent this filter form from setting the $dirty flag in commit #f6ec479