Thursday, 10 November 2011

QueryOverride and its quirks - use with caution!

This post deals with the quirks involved with the QueryOverride property in a Content Query Editor WebPart. Suffice it to say when they say "override" they mean it. It overrides almost everything you set for a Content Query Editor Web Part in your UI and if you are unprepared this is a massive PITA to find and fix. I was, naturally, unprepared and had to find and fix it.

It started with a request to filter an existing UI so that news items lingering longer than a month would not be displayed. A bit of digging and I soon learned that the "Modify Shared Web Part" in the UI cannot calculate back from [Today], only a fixed date, so I would have to select Export on the menu and save to my own hard drive to edit the CAML in notepad. Naïve as I was, I thought it would be simple enough to replace the QueryOverride line with:

<property name="QueryOverride" type="string" ><![CDATA[<Where><And><Geq><FieldRef Name='Modified'/><Value Type="DateTime"><Today OffsetDays="-30"/></Value></Geq></Where>]]></property>
OK. Well I uploaded it to the site collection web part gallery, recreated my web part on the page to point to it, and that seemed to work. Last April's stuff was no longer appearing on the list. Except that one of my colleagues mentioned, "Hey, the order looks funny". I had a look and indeed she was right. Instead of descending order, it went from earliest first. So I went "Edit Page" and modified the web part to switch from Ascending to Descending, clicked "Apply" and hey presto…it switched right back to "Ascending" again! I tried this several times, with the same result. More googling and nail biting. Eventually I found that I needed to specify not only WHAT I wanted back, but what order I wanted it in. So my QueryOverride line became:

<property name="QueryOverride" type="string" ><![CDATA[<Where><Geq><FieldRef Name='Modified'/><Value Type="DateTime"><Today OffsetDays="-30"/></Value></Geq</Where><OrderBy><FieldRef Name="Created" Nullable="True" Type="DateTime" Ascending="FALSE"/></OrderBy>]]></property>

RIGHT. Upload, delete web part, recreate. So now we had the correct items appearing in the correct order. Happy Days. Except then the user said, "Hey, we have another problem".

The CEWP I was editing was pulling all items from all site collections on the portal that had a particular page content type. One library had items which were not to be included in the main list. It had a different page content type. Everyone knew about this other page content type and used it. The main news webpart had always ignored it because it was pointing to a different content type. Everything had been fine. Up till the time I'd put in my QueryOverride property, when it had all gone to dog doodoo. The items from the not-to-be-included library were very much included, and what was I going to do about it, eh?

I looked at the CAML of the two web parts, the good one and the bad one (i.e. mine)  - I copied them both into Excel and eyeballed them there, for God's sakes - and could not see what the problem was. Both web parts had their respective content types clearly specified in the Presentation section. There could be no ambiguity. So what was going on?

I finally discovered that QueryOverride doesn't care what you put in your content types. Once I put in the "Modified" date in the queryoverride, I was toast. It just hoovered up everything from everywhere REGARDLESS of what I specified for content types in the UI and what was written in the CAML. I realised I would have to tell the bloody thing for once and for all - don't use that &*(^ing content type. So now here is the final all-singing, all-dancing QueryOverride line I had to use. Oh and note the location of the <And> tags. I stuffed that up the first time and wondered why the logic wasn't working...

<property name="QueryOverride" type="string" ><![CDATA[<Where><And><Geq><FieldRef Name='Modified'/><Value Type="DateTime"><Today OffsetDays="-30"/></Value></Geq><Neq><FieldRef Name="ContentType"/><Value Type="Text">[My Little Content Type]</Value></Neq></And></Where><OrderBy><FieldRef Name="Created" Nullable="True" Type="DateTime" Ascending="FALSE"/></OrderBy>]]></property>

Please note that I have put the content type name in square brackets. I don't think my employer would care for me to put in the real content type name!

All right, good to go at last, surely! But no, not yet. The news items weren't displaying the way they were in the last web part, where we'd seen a nice little teaser under the headline. So I stuck the thing into Excel (again) to compare and amended the following CAML parameters:

<property name="ParameterBindings" type="string" null="true" />
<property name="CommonViewFields" type="string">PublishingPageContent</property>
<property name="NoDefaultStyle" type="string" null="true" />


Finally! It worked!

5 comments:

  1. I have just discovered this post, and having similar problems.
    I have used the CQWP and Override the query. First time I asked for all ContentType Eq "MyContentType". This returned results. I then override the query just to bring back all Title Eq "MyTitle". Again this brought back results. All the results it brought back were of content type MyContentType. But when I Override the query to all ContentType Eq "MyContentType" and Title Eq "MyTitle" I ended up with no results, even though I should have.

    From reading your blog here, I changed the ParametBindings and NoDefaultStyle by adding null="true" to both of them, but it didn't help. What you didn't explain in your blog was how you got to the conclusion of changing these (Apart from a comparison you made), and why changing these values makes it work. You are the only person who blogged about something similar to what I'm trying to achieve, please can you help?

    ReplyDelete
  2. Hi Paul!

    Sorry only got to this now, was in work etc.

    The ParentBinding stuff is more for cosmetic purposes. The Content Type and Title elements would probably be in the QueryOverride. Can you post the CAML? I had a problem with my And tags being in the wrong place so it might be something as simple as that...

    ReplyDelete
  3. Susan, Just re-read my post. The XML didn't display.
    I'm hoping this does.
    <OrderBy><FieldRef Name="Title" Type="Text" Ascending="TRUE" /></OrderBy><Where><And><Or><Or><Eq><FieldRef ID="c042a256-787d-4a6f-8a8a-cf6ab767f12d" /><Value Type="Text">MyDocument</Value></Eq><Eq><FieldRef ID="c042a256-787d-4a6f-8a8a-cf6ab767f12d" /><Value Type="Text">PR Material</Value></Eq></Or><Eq><FieldRef ID="c042a256-787d-4a6f-8a8a-cf6ab767f12d" /><Value Type="Text">Engagement Letter</Value></Eq></Or><And><Contains><FieldRef ID="7fc1985b-d7ff-4389-a865-f85a97c9b3e6" /><Value Type="Note">Alstom|e90fbfcf-35d4-43f5-a7ea-4bc2f91daeaf</Value></Contains><Contains><FieldRef ID="24857027-b36a-497c-9ca3-5785379927eb" /><Value Type="Note">Terms of engagement|04dce3ab-ee11-48c8-b0c3-e97080ee1274</Value></Contains></And></And></Where>

    ReplyDelete
  4. That looks like a very complex piece of logic. If I were in your position I would build the CAML condition by condition until you get to the point where the logic fails. Also I am presuming the value type note is referring to a comments field, yes?

    I know it's a pain in the neck but at least then you have some chance of replicating the error.

    Also I don't see any reference to Content Type in your CAML. As this will override anything in the UI you will probably need to put it in if you want filtering.

    Good luck!

    ReplyDelete
  5. This comment has been removed by the author.

    ReplyDelete