How to use In Operator in PnP

[spvjebaraj]

Hi, Is it possible to use "In" operator in pnp where we used in CAML Query as below.

`

        <Values>${idValues}</Values>
      </In>`

$idValues contains the below CAML and it is generated in for loop. idValues += <Value Type="Number">${id}</Value>

If not how to achieve it in pnp?. Please help me.

[koltyakov]

I would say REST rather than PnPjs. I usually use following pattern:

const filterCond = [10, 20, 30, 40, 50].map(id => `Id eq ${id}`).join(' or ');
items.filter(filterCond).get().then(console.log);

[spvjebaraj]

Thanks Andrew. Shall I use the above filter condition for 200 items?

[koltyakov]

I wouldn't recommend such long conditions really. Not sure that there is a silver bullet solution for this. Conditions splitting, batch requests, requests structure reorganizations, ahead of time data preparation, combinations with CAML methods (get by CAML, renderListData's methods), common sense and experiments for a specific dev use-case, all of these are applicable.

[koltyakov]

How can it be that some entity should be filtered by 200 something values?

[spvjebaraj]

In my scenario, I have filter drop down to select 100 or 200 items. I am using items.getPaged () method to get the 100 or 200 items, from this result set I want to get information from another list based on this ID. At this time I want to use "IN" operator and the resulted item should be paginated.

[spvjebaraj]

Hi Andrew, I am using the below code to get the items by batch. But I am unable to retrieve the items from list. Is anything wrong in the code. I am using Angular 5 that's why I used Observable here.

const batch = sp.createBatch();

`const items = sp.web.lists.getByTitle(this.listTitle).items
  .select('ID, Title', 'Author/ID', 'Author/Title', 'Editor/ID', 'Editor/Title')
  .expand('Author,Editor')
  .orderBy('ID', false);

items.inBatch(batch)
  .get();

return Observable.fromPromise(batch.execute());`

[koltyakov]

Kind of a weird example but it represents how batch flow works:

const batch = sp.web.createBatch();

const items = sp.web.lists.getByTitle(_spPageContextInfo.listTitle).items.inBatch(batch);

let results = []; // Cache variable to aggregate results from batched responses
                  // The same approach is applicable for errors

// Results data is resolved in original promises
items.filter('Id eq 1').get().then(r => results = results.concat(r)); 
items.filter('Id eq 2').get().then(r => results = results.concat(r));

batch.execute().then(_ => { // In batch execute response there is no results data
  console.log(results);
  // On batch promise response all the batched promises are already resolved
});

[patrick-rodgers]

Hi @spvjebaraj - also would recommend against doing something like getting 200 items from a list and then trying to get matching items by ID from another list like this. Instead you could combine the lists, or if that isn't an option add a lookup column and associated fields from one list to the other so you can do everything in a single query. HTH

[spvjebaraj]

Thanks Patrick and Andrew. The above code is working fine.

[patrick-rodgers]

Great, thanks @spvjebaraj . Will close this as answered then, should you need to continue the conversation please reopen the issue. Thanks!