Filtering a CAML Query by Target Audience


This post is not really my usual area of expertise, but hopefully it might be useful to someone who is trying to do something along the same lines.

Recently I had the opportunity to work on a Silverlight control to be displayed on a SharePoint Intranet site. The control was pulling data from a SharePoint list via a Collaborative Application Markup Language (CAML) query and then filtering the results to select only the items with the required Target Audiences. This approach had some performance problems as it was necessary to return a large number of results to ensure that the required number of filtered results was contained within the result set.

So, for example, it would have to return 100 results to filter it down to 10 results for the required target audience. Needless to say this is a not a great idea. A better approach would be filter the list on the server as part of the CAML query and then return only the results we were interested in.

So, just how would you go about adding some audience targeting filters to a CAML query, where there could be one or more possible target audiences?

The query would effectively become (in SQL like syntax):

SELECT * FROM LIST WHERE TargetAudiences IN (AudienceList)

However, SharePoint lists don’t really allow this type of query. For an item in a list the audiences are a string of audience Guids, separated by either commas or semi-colons (this article explains why this is the case) so the IN bit cannot be used. The query becomes something like

SELECT * FROM LIST WHERE TargetAudiences LIKE %AudienceGuid1% OR TargetAudiences LIKE %AudienceGuid2% OR TargetAudiences LIKE %AudienceGuid3% ... etc

CAML does not have a LIKE, but it does have a Contains which is close enough. So all I have to do is build up the a query string that is valid, and do it such a way that it can work for 1 audience, for 20 audiences of however many.

I am just going to come out and say it. The CAML logical join (AND / OR) operators are weird. The operators have to be applied in pairs (maybe in homage to Noah’s Ark?)

A single OR with 2 conditions:

<Or>
    Condition1
    Condition2
</Or>

3 conditions:

<Or>
    <Or>
        Condition1
        Condition2
    </Or>
    Condition3			
</Or>

This can lead to some spectacularly ugly nested conditions, that are both hard to write and hard to read. You know when a languages syntax is bad when there are lots of query building and converting tools available to give you a fighting chance to use it correctly.

I needed to generate a query that could have an arbitrary number of Contains statements OR’d together from a list of Audience guids provided in a List (it doesn’t really matter where the List came from).

A contains query looks like this:

<Contains>
    <FieldRef Name='Target_x0020_Audiences'/>
        <Value Type='TargetTo'>11111111-1111-1111-1111-111111111111</Value>
</Contains>

A contains query like this is not ideal, as its performance is poor due to the string matching it has to do. I am not sure that there is any other way to do this that would perform better. If there is let me know in the comments, I would love to know.

If we wanted to just look for a single audience, the query above would be fine, but I needed to look for lots of audiences and get the results that matched at least one of them. To create the query, a small utility class was in order. The basic idea was to create the Contains syntax for each Guid and push it onto a queue, then take the top 2 items and OR them together before pushing the result back on the queue. Repeat this until there was a single item in the queue, which is the query that we want.

So, if A, B C etc. represent a Contains condition, and the () represent the OR tags, the process for five audiences looks like this:

----> Head
E D C B A --> (AB) E D C --> (CD) (AB) E --> ((AB)E) (CD) --> ((CD)((AB)E))

The code from the utility class:

public static string CreateTargetAudienceCriteria(List<string> audienceList)
{
    var audienceQueue = new Queue<string>();

    audienceList.ForEach(audience => audienceQueue.Enqueue(BuildContains(audience)));

    while (audienceQueue.Count > 1)
    {
        audienceQueue.Enqueue(String.Format("{0}{1}{2}{3}""<Or>\r\n", audienceQueue.Dequeue(), audienceQueue.Dequeue(), "</Or>\r\n"));
    }

    return audienceQueue.Dequeue();
}

private static string BuildContains(string audience)
{
    return String.Format("<Contains>\r\n<FieldRef Name='Target_x0020_Audiences'/><Value Type='TargetTo'>{0}</Value>\r\n</Contains>\r\n", audience);
}

And it would be simply used within a CAML query as shown below. It is likely that your query would include more in the Where clause:

var camlQuery = new CamlQuery
{
	ViewXml = string.Format(
		@"<View>  
			<Query> 
				<Where>
					{0}                        
				 </Where>
			</Query> 
		</View>",
	TargetAudience.CreateTargetAudienceCriteria(audienceList))
};
Advertisements

3 Responses to Filtering a CAML Query by Target Audience

  1. Adam says:

    There’s an operator now.

  2. Adam says:

    IN operator.

    • jheppinstall says:

      Hi Adam, thanks for your comment. As far as I understand CAML (which is not a great deal) it is not possible to use the IN clause when dealing with audience guid’s, as they are contained in a string that contains one or more guid separated by semi-colons. If it was just a single guid value then I think IN could be used, however this is not the case.

      If you have an example of how to use the IN clause for this situation I would love to see it, as it would make my code a lot more straight forward.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: