CAML Query Builder

Applies to

Akumina Foundation 3.4 and above

Akumina.CamlQueryBuilder.Build

The CAML Query Builder is an addition to the Akumina Framework that provides an easy way of generating CAML Queries. This frees up the developer from having to write XML by hand, or Googling CAML syntax to get a valid CAML query.

The CAML Query Builder object only requires two inputs, an Akumina.CamlQueryCriteria object, which contains the properties that will be used in the query, and the rowLimit, which is simply the number of rows that will be returned from the list. A proper call to the CAML Query Builder will follow the format below

Example 1:

In our first example, we query on an item by AkId as well as which matches a title.

var q = new Akumina.CamlQueryCriteria();
q.ListName = "CompanyNews_AK";
q.SelectFields = ["Title","Summary"];
var exp = (new Akumina.PropertyExpression("Title").EqualTo("Featured In the News_Company Awards"));
var exp2 = new Akumina.PropertyExpression("AkId").EqualTo(1);
q.Filter = (exp.And(exp2));
q.Filter = exp;
var query = Akumina.CamlQueryBuilder.Build(q, 5);

This yields the query

<View><ViewFields><FieldRef Name="F1"/></ViewFields><Query><Where><Eq><FieldRef Name="Title"/><Value Type="Text">Featured In the News_Company Awards</Value></Eq></Where><OrderBy></OrderBy></Query><RowLimit>5</RowLimit></View>

Example 2: End to end using SharePoint JSOM

Here we will insert a CAML query built buy the Akumina.CamlQueryBuilder into a SharePoint JSOM function accessing a list. The original example can be found at https://msdn.microsoft.com/en-us/library/office/hh185007(v=office.14).aspx

function retrieveListItems() {
    
	var q = new Akumina.CamlQueryCriteria();
	q.ListName = "CompanyNews_AK";
	q.SelectFields = ["Title","Body", "ID"];
	var exp = (new Akumina.PropertyExpression("Title").EqualTo("Featured In the News_Company Awards"));
	var exp2 = new Akumina.PropertyExpression("AkId").EqualTo(1);
	q.Filter = (exp.And(exp2));
	q.Filter = exp;
	var query = Akumina.CamlQueryBuilder.Build(q, 5);

	var clientContext = new SP.ClientContext();
	// set the listname
	var oList = clientContext.get_web().get_lists().getByTitle(q.ListName);
		
	var camlQuery = new SP.CamlQuery();

	// Set the viewXML to the built CAML
	camlQuery.set_viewXml(query);
	this.collListItem = oList.getItems(camlQuery);
		
	clientContext.load(collListItem);
		
	clientContext.executeQueryAsync(
	  Function.createDelegate(this, this.onQuerySucceeded),
	  Function.createDelegate(this, this.onQueryFailed)
	);        
		
}

function onQuerySucceeded(sender, args) {

	var data = {};
	data.Items = [];

	var listItemEnumerator = collListItem.getEnumerator();
		
	while (listItemEnumerator.moveNext()) {
		var oListItem = listItemEnumerator.get_current();
		var listItemInfo =
		{
			"ID": oListItem.get_id(),
			"Title": oListItem.get_item('Title'),
			"Body": oListItem.get_item('Body')
		};
		data.Items.push(listItemInfo);
	}

	console.log(data);
}

function onQueryFailed(sender, args) {

	console.log('Request failed. ' + args.get_message() + '\n' + args.get_stackTrace());
}

Akumina.CamlQueryCriteria

The CAML Query Criteria object contains all properties needed for the CAML Query Builder to construct your query. These properties are explored below

ListName

The name of the list you are querying. This is passed as a string

Example

var criteria = new Akumina.CamlQueryCriteria();

criteria.ListName = "Sample_AK";

SelectFields

The fields we are retrieving from the list. This is as a string array.

Example

var criteria = new Akumina.CamlQueryCriteria()

criteria.SelectFields = ["ID", "Title", "AkNumber"];

Filter

The query filter expression. All values that you would expect to find between the <Where></Where> tags within a query. The Filter is constructed through the use of the Akumina.PropertyExpression object

Akumina.PropertyExpression

The Akumina.PropertyExpression object creates an expression based a column name within the list. Expressions can be generated through the following examples

Contains

var exp = new Akumina.PropertyExpression("Title").Contains("akumina");

This yields the Query Filter expression

<Where><Contains><FieldRef Name="Title"/><Value Type="Text">akumina</Value></Contains></Where>

EqualTo

var exp = new Akumina.PropertyExpression("Title").EqualTo("akumina");

This yields the Query Filter expression

<Where><Eq><FieldRef Name="Title"/><Value Type="Text">akumina</Value></Eq></Where>

NotEqualTo

var exp = new Akumina.PropertyExpression("AkId").NotEqualTo(1);

This yields the Query Filter expression

<Where><Neq><FieldRef Name="AkId"/><Value Type="Text">1</Value></Neq></Where>

GreaterThan

var exp = new Akumina.PropertyExpression("AkId").GreaterThan(1);

This yields the Query Filter expression

<Where><Gt><FieldRef Name="AkId"/><Value Type="Text">1</Value></Gt></Where>

GreaterThanOrEqualTo

var exp = new Akumina.PropertyExpression("AkId").GreaterThanOrEqualTo(1);

This yields the Query Filter expression

<Where><Geq><FieldRef Name="AkId"/><Value Type="Text">1</Value></Geq></Where>

LessThan

var exp = new Akumina.PropertyExpression("AkId").LessThan(1);

This yields the Query Filter expression

<Where><Lt><FieldRef Name="AkId"/><Value Type="Text">1</Value></Lt></Where>

LessThanOrEqualTo

var exp = new Akumina.PropertyExpression("AkId").LessThanOrEqualTo(1);

This yields the Query Filter expression

<Where><Leq><FieldRef Name="AkId"/><Value Type="Text">1</Value></Leq></Where>

And

exp = (new Akumina.PropertyExpression("Title").Contains("akumina"));
exp2 = new Akumina.PropertyExpression("AkId").EqualTo(12);
andExp = (exp.And(exp2));

This yields the Query Filter expression

<Where><And><Contains><FieldRef Name="Title"/><Value Type="Text">akumina</Value></Contains><Eq><FieldRef Name="AkId"/><Value Type="Text">12</Value></Eq></And></Where>

Or

exp = (new Akumina.PropertyExpression("Title").Contains("akumina"));
exp2 = new Akumina.PropertyExpression("AkId").EqualTo(12);
orExp = (exp.Or(exp2));

This yields the Query Filter expression

<Where><Or><Contains><FieldRef Name="Title"/><Value Type="Text">akumina</Value></Contains><Eq><FieldRef Name="AkId"/><Value Type="Text">12</Value></Eq></Or></Where>

OrderBy

The order in which we want to retrieve rows based on their fields. This is passed as an array of Akumina.SortDirection objects.

var criteria = new Akumina.CamlQueryCriteria()
var orderby = [];
orderby.push(new Akumina.SortOrderAndDirection("Title", Akumina.SortDirection.Ascending));
orderby.push(new Akumina.SortOrderAndDirection("AkId", Akumina.SortDirection.Descending));
criteria.OrderBy = orderby;

LanguageId

The id of the content langugage to be retrieved from the list. This is a numeric value and can be retrieved from the UserContext.

Numeric

var criteria = new Akumina.CamlQueryCriteria()
//language code (Croatian)
criteria.LanguageId = 1050;

UserContext

var criteria = new Akumina.CamlQueryCriteria()
//language code from UserContext
criteria.LanguageId = Akumina.Digispace.UserContext.LanguageId;