Querying for current user’s items using the Content by Query Web Part’s QueryOverride property

We have all learned to love and adore the Content by Query Web Part (CQWP) for showing content from our site collection without having to put our hardcore developer gloves on. It’s great for easily showing content of a certain kind, from a certain site or belonging to a certain user. We’re usually configuring the web part using the three filter properties of the web part in order to show the content we’re looking for.  Unfortunately, sometimes three filter properties are not enough.

image

Say we have several lists of a certain content type with the three columns Status, Responsible and Assigned. We want to show items from this list where status is equal to ‘Open’, and the current user is either in the Responsible-column or in the Assigned-column. We are unable to use the web part’s properties to filter the content, because the three filter properties of the web part is not enough for us to formulate the query. We want items where Status=Open AND (Responsible=Current User OR Assigned=Current User). Unfortunately the filters will be interpreted as (Status=Open AND Responsible=Current User OR Assigned=Current User). This will give us all items where Status is Open and Responsible is Current User, but it will also give us all items where Assigned is Current User, independent on the Status! We don’t have any possibility to change which expression is being interpreted first. This leads us to the QueryOverride property. (Never mind that the MSDN documentation is awful and incorrect: “Gets or sets the list field name used to filter the set of list items.”?)

The QueryOverride property allows us to write good, old CAML. You can try writing the CAML-queries by hand, but I recommend checking out CAML Designer, SharePoint CAML Query Helper or some other tool to help you write those hairy CAML-queries.

Now to the point of this post. Using tools like the above or by looking up resources in various tech forums, the CAML used to filter items by the current user leads us to the following CAML:

<Where>
   <Eq>
      <FieldRef Name='Responsible' /> /*Some will add LookupId=’TRUE’ here. */
      <Value Type='Integer'>
         <UserID Type='Integer' />
      </Value>
   </Eq>
</Where>

This looks great, right? Well, it’s actually not working in the QueryOverride-property (the generated CAML is working in the CAML-tools and data queries). After a lot of experimenting, I found out that you cannot look up the Integer value of the user when filtering on the current user! Fortunately, only minor changes are necessary to get it to work. Change Type from Integer to User, and remove the type attribute from the UserID tag. The working CAML should look like the following:

<Where>
   <Eq>
      <FieldRef Name='Responsible' />
      <Value Type=’User’>
         <UserID />
      </Value>
   </Eq>
</Where>

And the full CAML to find all items with status ‘Open’ and with the current logged in user as assigned or responsible?

<Where> <And> <Eq> <FieldRef Name='Status' /> <Value Type='Choice'>Open</Value> </Eq> <Or> <Eq> <FieldRef Name='Responsible' /> <Value Type='User'> <UserID/> </Value> </Eq> <Eq> <FieldRef Name='Assigned' /> <Value Type='User'> <UserID/> </Value> </Eq> </Or>

</And> </Where>

A final note on how to use the QueryOverride property: In your .webpart file you should wrap the CAML in the CDATA tag, and you should remove spaces and line breaks. If you’re deploying your web part to a page through a module, you need to HTML-encode the CAML. The easiest way I have found to do this is to deploy the .webpart file with the Queryoverride property set, and then add the webpart to a page. You should then be able to export it, and the QueryOverride-property will have it’s content encoded, which you can reuse.