Building Multi-Level CAML Queries from GET Parameters in the URL
I had an interesting challenge just recently which was to perform multi-level queries on any given list dynamically using GET parameters to build the query. In other words, from a GET request, we should be able to search a list with multiple groups of ANDs & ORs just by changing the URL.
You can of course do this to some extent already with URL parameters but it’s much more limited that we needed; maybe we just want “thisField=thisValue” but maybe a much more complicated search too or perhaps even nothing at all. This system wouldn’t allow for every possibility of query complexity but certainly much more than you get by default.
Anyway it was interesting enough to share so I’m sharing – source code and all.
How to Store Multi-Level Queries in the URL
The first thing we had to do was figure out what format the URL parameters would use to fit everything we wanted to search for in. Here’s an example search scenario that the code would have to accommodate:
Food |
Storage |
Advertising |
|||
Pasta Rice Sauce |
OR OR |
Ambient Frozen Cooled |
OR OR |
TV Outdoor Radio |
OR OR |
AND |
AND |
We can shorten that table above to just this while achieving the same functionality requirements, for simplicities sake:
Food |
Storage |
Advertising |
|||
Pasta Sauce |
OR |
Ambient Frozen |
OR |
TV |
|
AND |
AND |
And so here’s how we would store that in a URL.
1. Written as a SQL statement, our search table would look like:
(Food=Pasta OR Food=Sauce) AND (Storage=Ambient OR Storage=Frozen) AND (Advertising=TV)
2. Let’s now assume that different field clauses will be “AND” and between different field values we’ll have “OR” – now the query could be written as:
Food=Pasta Food=Sauce Storage=AmbientStorage=Frozen Advertising=TV
3. Next we need to replace the spaces with something HTTP friendly. “~” for example, so now we have:
Food=Pasta~Food=Sauce~Storage=Ambient~Storage=Frozen~Advertising=TV
4. We can’t use the character “=” to separate out field values (well we could be for this example I wanted one “query=X” URL parameter. Replace “=” for “-eq-” & now we have:
Food-eq-Pasta~Food-eq-Sauce~Storage-eq-Ambient~Storage-eq-Frozen~Advertising-eq-TV
5. There we go; we have something that can fit in a URL, amongst other URL parameters. Here’s an example URL
https://server/page.aspx?query=Food-eq-Pasta~Food-eq-Sauce~Storage-eq-Ambient~Storage-eq-Frozen~Advertising-eq-TV&mode=edit&anotherField=value
Read the Filter Values in C# and Use in a SharePoint Query
Next up, and kinda more difficult is reading that format back in & generating the equivalent CAML XML. In theory it was easy but in practise much more difficult.
This is partly due to the fact, in CAML an “AND” or “OR” clause must only have 2 sub-conditions below it. Not one, three, or more; two and only two. Anything else will cause SharePoint to fail the query with “one or more field types are not installed” if you’re using an XSLT List View control (and probably others too) – a somewhat misleading error given is actually just a parsing error.
So what does that mean? Well if you have a requirement for this:
AND:
- Condition 1
- Condition 2
- Condition 3
…you need to write that in CAML as:
AND:
- Condition 1
- AND:
- Condition 2
- Condition 3
…which will give you the same result, but is just a bit of a pain to have to do. The same goes for “OR”.
Anyway worry not, the awesome code I built does all this for you automatically.
Code In Action
Here’s some actual examples the code is capable of producing this. Here we see the URL:
https://server/Pages/default.aspx?listName=/Lists/Test/AllItems.aspx&query=Food-eq-Pasta
This is a simple “field=value” requirement with the resulting CAML.
So far so good; the field is a choice field so the generated CAML reflects that automatically (as opposed to using “type=text” – another feature of the code no less.
Now let’s make the URL query more complex.
https://server/Pages/default.aspx?listName=/Lists/Test/AllItems.aspx&query=Food-eq-Pasta~Food-eq-Sauce~Storage-eq-Ambient~Storage-eq-Frozen~Advertising-eq-TV
That URL generates this CAML:
Notice how between each field we “AND” clause the different field conditions always, and between different values of the same field we “OR” clause the conditions. That’s a behavioural assumption right there; maybe you’ll want something else but this is how this code works anyway.
Also notice btw how there’s always and only even 2 sub-clauses per “AND”/”OR”. That’s the only way CAML can work for these types of queries but worry not, the code takes care of it all nicely in a recursive loop.
Here it is working on a publishing site with my test web-part.
You can use the functionality how you wish; in an SPList query or a XsltListViewWebPart view query as I’m doing here.
The Code & Key Classes
There are a few key classes at play here:
- WhereClause – the parent class.
- Normally this guy is all you’ll need to use – it can build itself from a query-string via static method “CreateFromQueryString” and it’ll spit out the CAML found in the URL with “ToCAML(SPList theListInQuestion)”.
- FieldClauseCollection – a bunch of “thisField=value1,value2,x,y” etc as a single class for X or more fields, using…
- FieldEqualsConditions – the list of conditions a field has.
All three have “ToString()” overridden so as to make sense in the debugger. It’s probably not the most perfect object model but it’s at least sort of organised. It’s mostly commented too! Bonus!
The rest is just a web-part that uses an XsltListViewWebPart to render the list with the query set as the view query dynamically from the WhereClause that’s instantiated.
Download Solution
This wee project is available here. Usual disclaimers apply: it comes without any guarantees whatsoever – the responsibility to ensure it won’t do bad things is entirely yours, etc, etc.
Hopefully someone finds this useful; setting complex queries via GET parameters is kinda cool and this code should make converting them into CAML a bit more palatable.
Cheers!
// Sam Betts