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