I know that this query works fine

SPQuery query = new SPQuery();
query.Query = "<OrderBy><FieldRef Name='ItemChildCount' Ascending='FALSE' /></OrderBy>"; 

But my SPListItemCollection doesn't display what I'm expecting as a result.

Actual Dispaly in C#             Actual Display in CAML Query Builder

ID | Title | ItemChildCount|     ID | Title | ItemChildCount |
---|-------|---------------|     ---|-------|----------------|
11 | Abc   |    11;#5      |     11 | Abc   |       5        |
---|-------|---------------|     ---|-------|----------------|
22 | BBB   |    22;#9      |     22 | BBB   |       9        |
---|-------|---------------|     ---|-------|----------------|
24 | Cbc   |    24;#7      |     24 | Cbc   |       7        |

I wonder why in U2U CAML Query Builder, the sort by ItemChildCount doesn't work. I tried to sort the items by ItemChildCount yet it sorts the ID instead.

But, when I check on the result on C# code immediate window using SPListItem["ItemChildCount"] it displays the concatenated ID and the ItemChildCount resulting to something like this 11;#5.

How will I create a query where I can do some string manipulations on the CAML query. Something like this

Select * from ListCollection Order by ItemChildCount.Replace("11;#","")

If ever this is possible.

  • it looks like you use the reverse lookup column ? in all case, can you show us the definition of your column ? – Steve B Jun 28 '11 at 11:43
  • Please do not crosspost questions between sites. We should choose one question to keep and delete the other. See meta.stackexchange.com/questions/64068/… – Kit Menke Jun 29 '11 at 14:02
  • @Kit Menke: Sorry my bad, I didn't know that it is against the rule :). I was just desperate to find the solution that I'm looking for. Already deleted my other post on stackoverflow. Thanks for the heads up :) – Carls Jr. Jun 30 '11 at 1:56

I hate to say it, but I'm afraid I don't think this is possible using CAML. Even joining the list to itself and using a GroupBy element wouldn't help because GroupBy isn't equivalent to its SQL counterpart.

My suggestion would be to:

  • Query the Item ID and ItemChildCount from the list (unordered)
  • Create a dictionary keyed by ItemChildCount (stripped), with the value being an array of item IDs that have that ItemChildCount
  • Populate the dictionary with the queried data
  • Work through the dictionary's keys in descending order, querying the Items in batches using the In CAML operator

Example of batch query:

<Where><In><FieldRef Name="ID" /><Values><Value Type="Number">1</Value><Value Type="Number">3</Value></Values></In></Where>

P.S. Trying to turn SQL into C# and CAML makes two very disconnected parts of my brain try to work together in a particularly nauseating way. Bleurgh.


try this :

 var field = ItemChildCount.Fields.GetFieldByInternalName("ItemChildCount");     
 var actualCount = field.GetValueAsHtml(ItemChildCount[field.Id]);

  • 1
    Unfortunately that won't help with ordering the query results. :/ – Stu Pegg Jun 28 '11 at 12:49
  • The real problem I have is sorting the ItemChildCount. Please refer to my previous issue sharepoint.stackexchange.com/questions/15164/… – Carls Jr. Jun 29 '11 at 1:29
  • depending on the number of items you want to retrieve, you can move to a coded orderby using a code similar to the one I provided. But this will be at performance cost, so don't use this if you have a large number of items (or cache the result at least). Don't know if it can be achieved using a pure caml query... – Steve B Jun 29 '11 at 7:15
  • Yep I also doubt that this can be done using pure caml query. I'm also looking forward on solving this issue using camlex.net (camlex.codeplex.com). The list I'm working on right now is definitely growing fast because it is a discussion list for the intranet so optimization is also part of my concern :) – Carls Jr. Jun 30 '11 at 1:55

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.