Pnpjs: Get All Items from a List subfolder

[enti333]

• [ ] Enhancement
• [ ] Bug
• [X] Question
• [ ] Documentation gap/issue

Hello. Please I need some little help. I want to get all items from a List (not Document Library) subfolder. I was thinking if something like this will not work.
sp.web.getFolderByServerRelativeUrl("/Audits/Lists/Cases/").expand("Items").get().then

I know it is working for Folders also for Files but not for items.... Hmm....
What is the best way to get items from subfolder when list have more then 20 000 items ...the Caml query doesn't work with more then 5000 items....

Thank you.

[koltyakov]

Hey @enti333,

You can try filtering based on FileDirRef property:

import { sp } from '@pnp/sp';
const listUri = '/sites/site/Lists/list';
sp.web.getList(listUri).items
    .filter(`FileDirRef eq '${listUri}/SubFolder01'`)
    .get().then(console.log);

Within a filter or CAML condition, the filtering should be applied based on the indexed field(s) and the resulted dataset should be less than 5000 (after filtering on a first cond) to request work in a large list. See the API reference for the case of the large lists.

[enti333]

Hello Andrew. Thank you for your help. So This will work also for big list(more then 20 000 items) if in "SubFolder01" is not more then 5000 items? Thank you very much for you help.

[koltyakov]

if in "SubFolder01" is not more then 5000 items

Sure thing, NO! A view after applying the first filter condition must be less than the throttling limit.

[enti333]

Hello Andrew. Sorry Andrew but I do not understand it ....
When I use this Code in list which has more then 5000 items
sp.web.getFolderByServerRelativeUrl("/Audits/Lists/Cases/").expand("Folders").get().then
It will work for me without any problem
I was thinking if there is not such function
sp.web.getFolderByServerRelativeUrl("/Audits/Lists/Cases/").expand("Items").get().then
Which will also work but for items even if there are more then 5000 items...
I know that answer is NO....
So My second question was if is possible to make query to get items from subfolder if there are more then 5000 items in list....
Your query is perfect but it is not working when the list has more then 5000 items.... I know that you have created something like getall method and this method is working when you do not use Filter in query....But when I use GetAll without filter it is not effective to foreach 20 000 items....

So the question is...is possible to get items from subfolder when the list has more then 5000 items effective. Thank you for your help Andrew.

[koltyakov]

FileDirRef should have been an indexed field (at least it was before if I'm not mismatching) but it's not anymore. Yeah, it's not a helper in a large list.

Can suggest trying another approach with renderListDataAsStream:

import { sp } from '@pnp/sp';
const listUri = '/sites/site/Lists/list';

sp.web.getList(listUri)
    .renderListDataAsStream({
        ViewXml: '',
        FolderServerRelativeUrl: `${listUri}/SubFolder01` // <--- sub folder URI
    })
    .then(console.log);

Will it help?

The alternative is not using folders in lists for filtering but custom indexed fields designed in combination with the application logic and large lists best practices.

[sympmarc]

Any approach to get more than 5000 items means paging your requests. Even a method like .getAll is going to be wrapping something which is doing the paging. If you want more than 5000 items, it simply is going to take longer.

If you can add a filter to your request to only get the items you need, you should. This is ALWAYS true - if you want your application to perform well. (Selecting only the columns you actually need is also important. Reduce those bytes down the line.)

[enti333]

Hello Andrew. Thank you very much your code it helps a lot. It is working also on big list....Andrew I can not find some text about this method renderListDataAsStream ...I don't know how it works and why it works?:) Yes my last chance was that I will create custom column and after the folder is created I insert the URL to folder in this custom column and I index this field...but your solution works:)
Thank you for your help.

Hello Marc. Yes I know but the problem was that I want to query maximum 40 rows but I need to run query on list which have 20 000 items...So I do not need to work wih 5000 items .... No I want to select 1-40 items but in big list and with caml or filter I was not able achieve this. Thank you for help.

[enti333]

I was testing this and I was thinking it is working:)and yes it is working but only with first 5000 items....
So if in my subfolder are items on more then first 5000 "position" the renderListDataAsStream doesn't work because the renderListDataAsStream is working only with first 5000 items in list...so it again return my nothing....This is very strange @koltyakov ...I try to specify the caml query but nothing it still work with first 5000 items in list....Thank you

[koltyakov]

If in a view is more than throttling limit it throttles. I guess it was written here at least three times. =)
I suggest closing the issue, that it's not about the library but the structure of the queries and dealing with the standard SharePoint limitations. And there are tons of articles about how to overcome the large lists requests.

[enti333]

Sorry @koltyakov you don't understand me.... Caml is written that should return only 3 items...so the result should not be 5000 items but only 3 items....but caml is runned only on first 5000 items....so the problem is that caml query for renderListDataAsStream is not runned on 20 000 items only on first (from ID) 5000 items.... Thank you...

[enti333]

It should be written somewhere that renderListDataAsStream is runned only on first 5000 items....Thank you for understanding.

[koltyakov]

Caml is written that should return only 3 items

How do you know this? Do you use an indexed field as a first condition filter and after applying that first condition is it less than 5000 items?

renderListDataAsStream is runned only on first 5000 items

There is pagination in this method as well, btw.

[sympmarc]

It might help if you post your CAML with the specific information about which items you want, @enti333. It sounds like your filtering isn’t set up to do what you’re trying to do.

[patrick-rodgers]

Going to close this due to inactivity. It appears the question was answered within the thread. Should you need more assistance here please open a new issue and link this one and let us know you're still stuck. Thanks!