Tridion pagination - getting the total number of results

Question

We are writing some code to control the pagination of results returned from a Tridion Broker database query (using the API).

We are using SDL Tridion 2011 SP1 and can use the PagingFilter to get the tcmIds of just the Components on the selected page.

However, whilst writing out the pagination control we need to know the total number of results (to determine how many pages there will be). Is there a more efficient mechanism for doing this than just running a separate query for 'all' results and doing a .Length on the string array returned? (Obviously you would only run this query once and persist that value as the user clicks between pages.)

If we are getting all of the results then why would I bother using the PagingFilter when we can just process the information returned in the 'all' query?

Many thanks in advance, Jonathan

NOTE: There will probably be a maximum of 2000 results of any one type returned.

Sonorous

I understand former content delivery-returned objects were cached but now (exact matching) broker queries are cached (since Tridion 2011?)--maybe that could be a reason to use a specific filter? I've seen JavaScript-powered pagination, but not sure on ideas better than your two query approach. – Assist

Great question Jon, doesn't look like you can do a "COUNT(....)" style query. – Farther

Thanks Neil. Yes, we thought that there might be a more efficient COUNT(...) mechanism too. The PagingFilter approach is very useful if you KNOW how many pages you will have or don't need to know how many pages (and handle empty arrays being returned effectively), but I'd have thought that this is quite rare. – Sonorous

A common trick with such an API is to request one more item than you intend to display (so request 51 items when you display 50). That way you know to display the Next button when you get that 51st item. – Cleodal

Answer 1

I have 3 possible answers for you, although none might be the right one or the one you want.

1. There is no way using the CD API to read the COUNT of returned items. You could write come kind of extension. Be it a CD Storage Extension, or a direct DB query, etc.

2. You read the exact number of items you have in your collection. This is particularly tricky in case you are using query for Components, with the intention to retrieve DCPs for these Components. It might be that there is no DCP for a given Component, therefore you need to read all DCPs first in order to know the exact number of items you want to paginate for. Obviously this will defeat the whole purpose of pagination. You might alleviate this performance drop with running the query once, then cache it for a while, but depending on what you query on, it might be that each website visitor queries for different terms, thus performance hit is high.

3. You don't really care about the total number of items in the pagination. So for example, rather than displaying "Page 1 of 23", "Page 2 of 23", etc... you would simply display Page 1, Page 2 with their Next and Prev buttons next to it.

Hope this helps!

Jointly

Thanks Mihai. I had not considered the impact of having no DCP for a Component (as you highlight in your second suggestion). I like the CD Storage Extension idea (as we may have to allow the website users to filter at a later date). However, for now I think that we will probably implement suggestion 2 and use caching. Many thanks! – Sonorous

Answer 2

During the publishing of your component you may implement a TBB that counts all your published components, then publishes the result in a text or XML file as a binary that you read using standard system.io functions. You could also publish a separate DCP specifically to hold the count (you'd then need a schema and a component for each publication).

The idea is to determine the Count at render time and somehow publish that number out. Pulling a single number out on th presentation side would certainly be faster than pulling 2000 DCPs.

Selah

Thanks Nickoli. I think that this is a good answer and agree that doing this at publish time would be more efficient. I think that it would be good to come up with a 'best practice' eXtension implementation for this to get some consistency between Tridion clients.... Just need to find time now! – Sonorous