3

Let's assume I have list with 100 items. Is it possible to write CAML query to get 5 items, but skip 10 items from the beggining? Something like LIMIT and OFFSET in SQL?

3

Unfortunately, SharePoint paging works a bit differently than LIMIT and OFFSET in SQL. To get paginated items you have to provide ID of the first item for the page you want and then set RowLimit, and if you have sorting defined for the list, you also have to provide sorting columns names and values of those columns for the first item on your page.

You can see how it works if you go to any list which has enough items and try to go through pages and see how the query string of the page changes. Then delete some items and try again. Then change order of items and try again...

All those params you will have to pass into ListItemCollectionPosition property of SPQuery object.

More about paging in SharePoint:

http://www.directsharepoint.com/2011/03/step-by-step-guide-to-implement-paging.html

In your particular case, you can simplify things a bit, if you don't have filtering and sorting:

  1. Select first 11 IDs from the list
  2. Use 11th ID to create the correct ListItemCollectionPosition for your SPQuery
  3. Set RowLimit of the SPQuery object to 5

To quickly select top 11 IDs, use this query:

var query = new SPQuery()
{
    ViewFields = "ID",
    ViewFieldsOnly = true,
    IncludePermissions = false,
    RowLimit = 11
};
  • 1
    so in other words... the paging is unusable because you just cant skip to page number 10 for example... sweet – Michal Hainc Jun 22 '15 at 7:57
0

To skip n items is not possible, but you can perform a paged query, that means you can set a RowLimit with an Order and limit the result items. The result has a ListItemCollectionPosition property set, which defines where the next page begins.

Here you have a C# CSOM example:

int page = 2; // the desired page

string viewXml = $@"<View>
                        <Query>
                            <Where><Eq><FieldRef Name='FieldToFilter'/><Value Type='TypeOfField'>TheValue</Value></Eq></Where>
                        </Query>
                        <RowLimit>10</RowLimit> // Rowlimit is needed for paging, in your case to skip 10
                        <OrderBy>
                            <FieldRef Name='FieldToOrder' /> // In paged queries is advised to set an order
                        </OrderBy>
                    </View>";

using (ClientContext context = new ClientContext(YourUrl))
{
    CamlQuery camlQuery = new CamlQuery
    {
        ViewXml = viewXml
    };
    List list = context.Web.Lists.GetById(ListGuid);
    ListItemCollection items = null;
    while (page > 0)
    {
        items = list.GetItems(camlQuery);

        context.Load(items);
        context.ExecuteQuery();

        camlQuery.ListItemCollectionPosition = items.ListItemCollectionPosition;
        page--;
    }

    items.ToList();
}

This query returns item 11-20.
I did not test it in Javascript, but I'm sure it works too.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.