none
Sorting and paging with SPQuery at the same time RRS feed

  • Question

  • Hi, I have created a custom list webpart where I use SPQueries to fetch the data and do paging (using the SPListItemCollectionPosition class)

     

    I'm paging the list manually only getting the paged result. I add a SPListItemCollectionPosition object with the following value "Paged=TRUE&p_ID={0}" where {0} is the index I want to start from.

     

    When just using a simple Where query without sorting everything works like a charm, but when I throw the following query a the list something goes wrong. Everything is sorted, but I get the same page of results every time.

     

    Can someone help me?

     

    Code Snippet
    <OrderBy><FieldRef Name="Modified" Ascending="False" /></OrderBy><Where><Eq><FieldRef Name="DATASET" /><Value Type="Text">BOE</Value></Eq></Where>

     

     

     

     

    Friday, June 13, 2008 1:19 PM

Answers

  • Yes, it is not possible in SharePoint - it is same issue why SharePoint doesn't show how many pages you have in this view. There is only arrows prev and next page and you will know that you are on a last page only if next page arrow is not visible :)
    Tuesday, January 5, 2010 11:42 AM

All replies

  • Have you ever seen the U2U Caml builder tool? It is a great tool that helps build the Caml for you through a query builder interface. Anyways, it might help you fulfill your requirements. Plus, it is really good to learn best practices for Building Caml (i.e.: it will teach you for your future requirements also).

    http://www.u2u.info/SharePoint/U2U%20Community%20Tools/Forms/AllItems.aspx

     

    Friday, June 13, 2008 1:35 PM
  • Thanks for the quick answer.

     

    Yes I know the tool, both the Windows application and the DLL's that follow it. The above XML is actually generated using the U2U.SharePoint.CAML.Builder class.

     

    But that really does not help me that much Smile The query seems to work just fine, getting the first page (in this case 1-10 items) and sorting the result. But when I request the second set of results from 11-20, I get the same first result.

     

    So somehow the paging does not work any more.

     

    Is there some CAML node that I am unaware of?

     

    Friday, June 13, 2008 1:42 PM
  • Ok, I haven't figured this out yet isn't there anybody that can help?

     

    After a bit of tweaking, including the p_Modified parameter in the SPListItemCollectionPosition object like you see in the standard list view I now get one more row (2-11) and the the paging stops working again.

     

    Please help Smile

    Monday, June 16, 2008 9:47 AM
  • Christian,
    I am having the same problem, though i am sorting on a different field (custom date field for publication date). Seams like the p_ID refers to the items id and of cource the item ids get shuffeled when sorting the list on anything else then the id.
    The problem would be solved when you could use the item index in the list. I have not found the solution yet, but i will post it as soon as i have found it. Hope you do the same.

    Regards.
    Thursday, August 21, 2008 6:41 AM
  • Has anybody found any solutions yet? I just got the same issue, thanks.

    Monday, October 20, 2008 8:32 PM
  •  I haven't tried this yet (I am about to), but are you re-using the same spquery object? According to this blog you need to

    http://www.wirwar.com/blog/tag/spquery/

    Monday, May 18, 2009 9:07 PM
  • Hi,
    anyone has an idea how i can directly navigate to a specific page.
    Lets assume the RowLimit of the SPQuery is 10 and i just want to see page 3 with the items 21-30.
    If the ids of the items are not the same like the index of the items i need to get the id of the last item of the previous item as far as i know to be able to set the ListItemCollectionPosition object (query.ListItemCollectionPosition = new SPListItemCollectionPosition("Paged=TRUE&p_ID={id of item with index 20}");)

    Is there anyway to just tell the SPQuery to get item 21-30 (an not the items after item with id 21).
    Hope you know what i mean ;-)

    thanks for help,

    Greetings
    Monday, January 4, 2010 8:36 PM
  • Paging works this way:
    SPQuery query = new SPQuery();
    SPListItemCollection spListItems ; 
    string lastItemIdOnPage = null; // Page position.
    int itemCount = 2000
     
    while (itemCount == 2000)
    {
        // Include only the fields you will use.
        query.ViewFields = "<FieldRef Name=\"ID\"/><FieldRef Name=\"ContentTypeId\"/>";   
        query.RowLimit = 2000; // Only select the top 2000.
        // Include items in subfolder (if necessary).
        query.ViewAttributes = "Scope=\"Recursive\"";
        StringBuilder sb = new StringBuilder();
        // To make it order by ID and stop scanning the table, specify the OrderBy override attribute.
        sb.Append("<OrderBy Override=\"TRUE\"><FieldRef Name=\"ID\"/></OrderBy>");
        //.. Append more text as necessary ..
        query.Query = sb.ToString();
        // Get 2,000 more items.
     
        SPListItemCollectionPosition pos = new SPListItemCollectionPosition(lastItemIdOnPage);
        query.ListItemCollectionPosition = pos; //page info
        spListItems = spList.GetItems(query);
        lastItemIdOnPage = spListItems.ListItemCollectionPosition.PagingInfo;
        // Code to enumerate the spListItems.
        // If itemCount <2000, we finish the enumeration.
        itemCount = spListItems.Count;
    
    }

    code from:
    http://msdn.microsoft.com/en-us/library/bb687949.aspx

    I have used ListItemCollectionPosition in my applications and all works great.
    Monday, January 4, 2010 8:45 PM
  • to get 21-30 you should set RowLimit to 10 and in for-loop in 3rd iteration use result

    query.RowLimit = 10;
    SPListItemCollection col = null;
    for (int i = 0; i < 3; ++i)
    {
       col = list.GetItems(query);
       // here you should update query position
    }

    // here you can use col it will be on 3rd page.
    Monday, January 4, 2010 8:51 PM
  • Thanks for the fast replies.

    But these examples imply to iterate to all pages first to be able to page to the end of the list, because i need the last itemid before my requested page, right?
    Do you know if this is performant if we have around 10000 pages?
    In SQL i would be able to say something like: Select * from table where i = 1 POS 1000 COUNT 10
    That is not possible with the SPQuery object or?

    Greetings
    Tuesday, January 5, 2010 11:39 AM
  • Yes, it is not possible in SharePoint - it is same issue why SharePoint doesn't show how many pages you have in this view. There is only arrows prev and next page and you will know that you are on a last page only if next page arrow is not visible :)
    Tuesday, January 5, 2010 11:42 AM
  • ok, thats what i thought ;-)
    so we have to  live with that, but thanks anyway.
    cu
    Tuesday, January 5, 2010 6:12 PM
  • Nice..
    You can also visit my blog which explain how to apply paging with sorting,caml query, folder query on sharepoint list by using SPQuery and SPListItemCollectionPosition.

    SharePoint List Pagination using SPListItemCollectionPosition

    Thanks & Regards,
    Avinash
    My Blog: SharePoint Solutions
    • Edited by Avinash Dad Tuesday, April 10, 2012 7:44 AM Edited my blog link
    Tuesday, March 6, 2012 9:10 AM
  • I'm sure you've moved on from this long ago. But it took me a while to figure this out. And just for the benefit of anyone else looking, you can definitely do this.

    You need to include the sort field in your SPListItemCollectionPosition's PagingInfo property in this format: "Paged=TRUE&p_SortField=&p_ID=10".

    The easiest way to do this though, and the way I believe it was intended to work is to set the PagingInfo property to the PagingInfo property returned when loading the previous page of items.

    When you load items with SPList.GetItems() and the query returns more items than the row limit, the SPListItemCollection returned has a ListItemCollectionPosition property. If you set the ListItemCollectionPosition in the SPQuery for the next page to that value everything should work fine.

    SPListItemCollection items = list.GetItems(query);
    string pagingInfo = items.ListItemCollectionPosition.PagingInfo;
    
    var query = new SPQuery
    {
        RowLimit = 10,
        ListItemCollectionPosition = new SPListItemCollectionPosition(pagingInfo)
    };

    • Proposed as answer by Dan Hayman Wednesday, October 16, 2013 10:44 AM
    Wednesday, October 16, 2013 10:44 AM
  • Thanks!
    Thursday, August 8, 2019 1:41 PM