readme.md

List and field names are fictive.
Let's say we have a the classic contact and company list, contact has a lookup to company called "Company".
I want to search contact list based on company, .e.g., find all Contacts which are connected to Google.

See example code.

caml-test.js

var exec = (query) => {
  var c = SP.ClientContext.get_current();
  var q = new SP.CamlQuery();
  q.set_viewXml(`<View><Query><Where> ${query} </Where></Query></View>`);
  var items = c.get_web().get_lists().getByTitle('Contacts').getItems(q);
  c.load(items);
  return new Promise((resolve, reject) => {
    c.executeQueryAsync(() => resolve(items), (a, b) => reject(b));
  });
};

// This works
exec(`<Contains><FieldRef Name="FirstName" /><Value Type="Text">Sundar</Value></Contains>`).then(res=> {
  console.log(res.get_count());
  // 1
});

// This works if the list has less than 5000 items
exec(`<Contains><FieldRef Name="Company" /><Value Type="Lookup">Google</Value></Contains>`);

// This does not work if the list has more than 5000 items, throws threshold error
exec(`<Contains><FieldRef Name="Company" /><Value Type="Lookup">Google</Value></Contains>`);

// This works regardless, probably because the first text field filters the result:
exec(`<And>
      <Contains><FieldRef Name="FirstName" /><Value Type="Text">A</Value></Contains>
      <Contains><FieldRef Name="Company" /><Value Type="Lookup">Google</Value></Contains>
    </And>`);

// But if I swap the order of the And-expression it throw threshold error
exec(`<And>
      <Contains><FieldRef Name="Company" /><Value Type="Lookup">Google</Value></Contains>
      <Contains><FieldRef Name="FirstName" /><Value Type="Text">A</Value></Contains>
    </And>`);