Sort SPList on derived value of e.g. SPFieldUser column

Question

What I want to achieve is: get a bunch of SPListItems from an SPList, using SPQuery, and have the items ordered by an SPFieldUser column. Just referencing the SPFieldUser column appears to sort on the LoginName. My question is: is it possible to have it sorted on e.g. SPUser.Name, i.e. the real name of the user?

EDIT: Generally speaking, when sorting on e.g. a number field or a string field there are no surprises on the outcome, but what happens when you want to sort on a complex field like SPUser? SPUser.LoginName and SPUser.Name would result in different orderings in most cases.

Answer 1

The sorting of Lookup columns (and a SPFieldUser is just a lookup column into the User Information List) depends on the LookupField of the lookup column.

If you define your User field to display as one of the Name columns then the sorting of your Query should by the display name of the user.

Answer 2

ok, im presuming that you are creating the list using xml and not querying in .cs?

whats the full code and are you doing it like this?

<Query> 
  <OrderBy> 
    <FieldRef  Name="SomeUserField"/>
  </OrderBy> 
</Query> 

You dont need to add in ascending = true as by default it is ascending. You only need to use it if you want to go in decending order by setting it to false!

and that should be encapsulated in the view like so:

<View BaseViewID="0" Type="HTML" MobileView="TRUE" TabularView="FALSE"> 
<Toolbar Type="Standard" /> 
<XslLink Default="TRUE">main.xsl</XslLink> 
<ViewFields>
    <FieldRef Name="Title"/>
    <FieldRef Name="SomeUserField"/>
</ViewFields>
<Query> 
  <OrderBy> 
    <FieldRef  Name="SomeUserField"/>
  </OrderBy> 
</Query> 
</View> 

http://msdn.microsoft.com/en-us/library/microsoft.sharepoint.spsitedataquery.viewfields.aspx

for orderby query reference to this for help :)

orderby

http://msdn.microsoft.com/en-us/library/ms467378.aspx

full xml list (Query Schema)

http://msdn.microsoft.com/en-us/library/ms467521.aspx

hope this helps :)

EDIT

ok that would make sense :) , have you tried the following? tbh without seeing your code I wouldnt know where your going wrong!

SPQuery spQuery = new SPQuery();
spQuery.Query = "<OrderBy><FieldRef Name='SomeUserField' /></OrderBy>";

SPList list = web.GetList(listUrl);
SPListItemCollection items = list.GetItems(spQuery);

You can find more indepth here:

http://msdn.microsoft.com/en-us/library/ms457534.aspx

EDIT

If you want to replace the SPFieldUser to SPUser.name for the real name you need to cast or convert the object and loop through each one.

SPFieldUser userField = (SPFieldUser)item.Fields.GetField("LoginName");

SPFieldUserValue userValue = (SPFieldUserValue)userField.GetFieldValue(item["LoginName"].ToString());

SPUser user = userValue.User;

As an example the above code gets the spuser from the SPFieldUser. "LoginName" is the colum name your refering to change. Sorting on any list is possible, in the end its text your sorting on and its in a list!

for more of an understanding you should checkout this:

http://aarohblah.blogspot.co.uk/2009/11/converting-list-item-to-spuser-object.html

and

http://edinkapic.blogspot.co.uk/2007/08/getting-spuser-out-of-spfielduser.html

I hope its what your looking for or again iv missunderstood you lol.

hope it helps :)