Sharepoint: How to easily get related child items using JSOM

Question

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.

Vadim Gremyachev · Accepted Answer · 2014-09-15T20:05:21.797

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

Vadim, thanks for the example, but that doesn't address my problem. Unless I'm misunderstanding, your example would require a new query for Employees for each Team. I would like to get all the teams (potentially matching a criteria) and their employees. — New Dev, Sep 15 '14 at 18:36
In that case Joins and Projections is a way to go, the answer has been updated. — Vadim Gremyachev, Sep 15 '14 at 20:05
Nope, only Team list fields could be "projected" but no other way around. — Vadim Gremyachev, Sep 15 '14 at 20:57

score 0 · Answer 2 · answered Sep 14 '14 at 16:41

Probably you can not achieve what you want, because of lookup configuration. But you could do the following:

var ctx = SP.ClientContext.get_current();
var web = ctx.get_web();
var lists = web.get_lists();
var teams = lists.getByTitle("Teams");
var employees = lists.getByTitle("Employees");

//just get the first item
var employee = employees.getItemById(1);
ctx.load(employee)
ctx.executeQueryAsync(function() {
    var team = employee.get_item("TeamID");
    // both the id and the  value of the lookup field
    var lookupId = team.get_lookupId();
    var lookupValue = team.get_lookupValue();
    // let's grab all the fields
    var fullTeam = teams.getItemById(lookupId)
    ctx.load(fullTeam)
    ctx.executeQueryAsync({
        var name = fullTeam.get_item("Name");
        alert("We can get the Name field of the lookup field: " + name);
    }); 
});

I guess, it a bit reverse of what you really intent to achieve but still this way you will exploit CSOM.

Sharepoint: How to easily get related child items using JSOM

2 Answers2