Sharepoint:如何使用 JSOM 轻松获取相关子项目

Question

假设我有 2 个列表:团队和员工。每个团队有多名员工:

Teams
  ID
  Name

Employees
  ID
  Name
  TeamID (foreign key of Teams)

是否可以在 SharePoint JSOM 中编写查询，以便我可以按照以下方式执行某些操作(在查询执行/加载之后):

var employeesListItems = teamListItem.get_item("Employees")

SharePoint 对象模型是否以任何方式支持此功能？

澄清:我的目的是尽可能地重用 ClientObject。据我所知，我可以查询所有员工和所有团队，为每个员工创建一个自定义对象数组，然后迭代员工并将其推送到相关团队对象的“员工”字段中。我想避免这样做。

Answer 1

尽管 SharePoint CAML 支持 List Joins and Projections ，在这种情况下我会建议你采用不同的方法。

以下示例演示如何使用单个请求检索父/子项目:

function getItemWithDetails(parentListTitle,childListTitle,lookupFieldName,lookupFieldValue,success,error)
{ 
   var ctx = SP.ClientContext.get_current();
   var web = ctx.get_web();
   var lists = web.get_lists();
   var parentList = lists.getByTitle(parentListTitle);
   var parentItem = parentList.getItemById(lookupFieldValue);
   var childList = lists.getByTitle(childListTitle);
   var childItems = childList.getItems(createLookupQuery(lookupFieldName,lookupFieldValue));

   ctx.load(parentItem);
   ctx.load(childItems);
   ctx.executeQueryAsync(
     function() {
       success(parentItem,childItems);  
     },
     error
   );
}

function createLookupQuery(lookFieldName,lookupFieldValue)
{
   var queryText = 
"<View>" +
  "<Query>" +
    "<Where>"  + 
       "<Eq>" +
           "<FieldRef Name='{0}' LookupId='TRUE'/>" +
           "<Value Type='Lookup'>{1}</Value>" +
        "</Eq>" +
    "</Where>" +
 "</Query>" +
"</View>";   
    var qry = new SP.CamlQuery();
    qry.set_viewXml(String.format(queryText,lookFieldName,lookupFieldValue));
    return qry;
}

使用

var parentListTitle = 'Teams';
var childListTitle = 'Employees' 
var lookupFieldValue = 1;
var lookupFieldName = 'Team';

getItemWithDetails(parentListTitle,childListTitle,lookupFieldName,lookupFieldValue,
  function(teamItem,employeeItems){
     //print parent item 
     console.log(teamItem.get_item('Title'));
     //print child items
     for(var i = 0; i < employeeItems.get_count(); i++){
        var employeeItem = employeeItems.getItemAtIndex(i);
        console.log(employeeItem.get_item('Title'));
     }
  },
  function(sender,args){
      console.log(args.get_message());
  });

---

另一种选择是利用List Joins and Projections 。以下示例演示如何检索带有预计团队项目的员工列表项目

function getListItems(listTitle,joinListTitle,joinFieldName,projectedFields,success,error)
{ 
   var ctx = SP.ClientContext.get_current();
   var web = ctx.get_web();
   var list =  web.get_lists().getByTitle(listTitle);
   var items = list.getItems(createJoinQuery(joinListTitle,joinFieldName,projectedFields));

   ctx.load(items);
   ctx.executeQueryAsync(
     function() {
       success(items);  
     },
     error
   );
}


function createJoinQuery(joinListTitle,joinFieldName,projectedFields)
{
   var queryText = 
"<View>" +
  "<Query/>" +
  "<ProjectedFields>";
  for(var idx in projectedFields) {
    queryText += String.format("<Field Name='{0}_{1}' Type='Lookup' List='{0}' ShowField='{1}' />",joinListTitle,projectedFields[idx]);
  }
  queryText +=
  "</ProjectedFields>" +
  "<Joins>" +
      "<Join Type='INNER' ListAlias='{0}'>" +
        "<Eq>" +
          "<FieldRef Name='{1}' RefType='Id'/>" +
          "<FieldRef List='{0}' Name='ID'/>" +
          "</Eq>" +
        "</Join>" +
    "</Joins>" +
"</View>";   
    var qry = new SP.CamlQuery();
    qry.set_viewXml(String.format(queryText,joinListTitle,joinFieldName));
    return qry;
}

使用

var listTitle = 'Employees';
var joinListTitle = 'Teams' 
var joinFieldName = 'Team';
var projectedFields = ['ID','Title'];

getListItems(listTitle,joinListTitle,joinFieldName,projectedFields,
  function(employeeItems){
     //print items
     for(var i = 0; i < employeeItems.get_count(); i++){
        var employeeItem = employeeItems.getItemAtIndex(i);
        var employeeName = employeeItem.get_item('Title');
        var teamName = employeeItem.get_item('Teams_Title').get_lookupValue();
        console.log(employeeName + ',' + teamName);
     }
  },
  function(sender,args){
      console.log(args.get_message());
  });