How to query a list's user field for a given login name in Javascript?

Question

I need to build a query a custom list with a user field. The where clause filters the results to a given list of user login names of a dynamic length, so I build the query according to the numbers of users.

My problem is that executeQueryAsync returns every item of the list, no matter how simple the where clause is.

Example for the simplest query:

<View><Query>
   <Where>
     <Eq>
       <FieldRef Name="UserName"/>
       <Value Type="User">domain\username</Value>
     </Eq>
   </Where>
</Query></View>

What could be wrong?

Answer 1

You cannot use usernames in CAML queries to filter items based on the values of a Person field. You need to use either the full name or the id of the user.

If you only have the username, then you should get the user id first using the username and then execute the query to get list items using the user id that you got earlier.

Your code should look something like this:

var context = SP.ClientContext.get_current();
var web = context.get_web();
var user = web.ensureUser('DOMAIN\\USERNAME');

context.load(user);
context.executeQueryAsync(function() {

    var list = web.get_lists().getById('LIST_ID_HERE');
    var query = new SP.CamlQuery();
    var queryXml = '<View><Query><Where><Eq><FieldRef Name="Author" LookupId="TRUE"/><Value Type="Integer">' + user.get_id() + '</Value></Eq></Where></Query></View>';

    query.set_viewXml(queryXml);
    var items = list.getItems(query);

    context.load(items);
    context.executeQueryAsync(function() {

        // The list items has been retrieved.

    }, function() {});

}, function() {});

Your implementation may be a bit different, but you get the idea.

Answer 2

<Query>
   <Where>
     <Eq>
       <FieldRef Name="UserName"/>
       <Value Type='Integer'><UserID Type='Integer'/></Value>
     </Eq>
   </Where>
</Query>