Problem with CAML SPQuery ItemChildCount result

Question

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
– Steve B, Commented Jun 28, 2011 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
– Kit Menke, Commented Jun 29, 2011 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 :) — Ian Jasper
– Ian Jasper, Commented Jun 30, 2011 at 1:56

Stu Pegg · Accepted Answer · 2011-06-30 11:33:49Z

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.

Ian Jasper · Accepted Answer · 2011-06-29 03:19:56Z

0

try this :

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

edited Jun 29, 2011 at 3:19

Ian Jasper

1991 gold badge1 silver badge11 bronze badges

answered Jun 28, 2011 at 11:46

Steve B

7,5589 gold badges54 silver badges110 bronze badges

1

Unfortunately that won't help with ordering the query results. :/

Stu Pegg
– Stu Pegg

2011-06-28 12:49:56 +00:00
Commented Jun 28, 2011 at 12:49
The real problem I have is sorting the ItemChildCount. Please refer to my previous issue sharepoint.stackexchange.com/questions/15164/…

Ian Jasper
– Ian Jasper

2011-06-29 01:29:20 +00:00
Commented Jun 29, 2011 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
– Steve B

2011-06-29 07:15:03 +00:00
Commented Jun 29, 2011 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 :)

Ian Jasper
– Ian Jasper

2011-06-30 01:55:23 +00:00
Commented Jun 30, 2011 at 1:55

Add a comment |

Stack Exchange Network

Problem with CAML SPQuery ItemChildCount result

2 Answers 2

Your Answer

Linked

Hot Network Questions

Problem with CAML SPQuery ItemChildCount result

2 Answers 2

Your Answer

Sign up or log in

Post as a guest

Linked

Related

Hot Network Questions