sharepointsharepoint-2010sharepoint-2013sharepoint-clientobject

Sharepoint: How to easily get related child items using JSOM


Suppose I have 2 Lists: Teams and Employees. Each team has a number of employees:

Teams
  ID
  Name

Employees
  ID
  Name
  TeamID (foreign key of Teams)

Is it possible to write a query in SharePoint JSOM such that I could do something along the following lines (after the query executes/loads):

var employeesListItems = teamListItem.get_item("Employees")

Does SharePoint Object Model support this in any way?

Clarification: my intent is to reuse the ClientObject as much as I can. I understand that I could query for all employees and all teams, create an array of custom objects for each, and then iterate over employees and push them to onto the "Employees" field of the related Team object. I would like to avoid doing so.


Solution

  • Even though SharePoint CAML supports List Joins and Projections, in that case I would suggest you a different approach.

    The following example demonstrates how to retrieve parent/child items using a single request:

    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;
    }
    

    Usage

    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());
      });
    

    Another option is to utilize List Joins and Projections. The following example demonstrates how to retrieve employee list items with projected team items

    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;
    }
    

    Usage

    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());
      });