Filter a list by User or Group column in CAML Query

Question

Is there a way to filter a list by User or Group column?

I need to get all the tasks assigned to each user in a site.

I got the below code for now, but that only works if I'm trying to get my tasks.

Is there a way to pass an user ID to Membership Type so I can get the tasks of any user?

<Where>
<Or>
<Eq>
<FieldRef ID="AssignedTo" />
<Value Type="Integer">
<UserID/>
</Value>
</Eq>
<Membership Type="CurrentUserGroups">
<FieldRef Name="AssignedTo" />
</Membership>
</Or>
</Where>

Answer 1

You can use your query like below:

<Where>
   <Or>
      <Membership Type=’CurrentUserGroups’>
         <FieldRef Name=’AssignedTo’ />
      </Membership>
      <Eq>
         <FieldRef Name=’AssignedTo’  LookupId=’TRUE’ />
         <Value Type=’Lookup’>123</Value>
      </Eq>
  </Or>
</Where>

Here 123 is User ID of a SharePoint user.

The <Membership> tag contains an attribute “type” , this attribute can contains 5 static values , below the description for each one:

1. SPWeb.AllUsers : this value is used to identify the tasks assigned to users (not groups)

2. SPGroup : using this parameter you must add the group ID like that:

<Membership Type=”SPGroup”  ID=”7″>

This parameter will return list of tasks assigned to members of this group.

3. SPWeb.Groups : The tasks assigned to the group site collection appear but not the tasks assigned to specific groups site.

4. CurrentUserGroups : The tasks assigned to the current user groups will appear but not tasks assigned directly to the user.

5. SPWeb.Users : Tasks assigned to users who have received rights to the site directly (not through a group).

Reference:

Get tasks assigned to user or to current user groups in SharePoint using CAML query

Note: Using above query you can get the tasks assigned to particular user by passing user ID by replacing 123 in answer or you can get the tasks assigned to a group using <Membership Type=”SPGroup” ID=”7″> (Here 7 is SharePoint group ID).

Answer 2

Here is a function to filter by user id using the CSOM. The SSOM code is similar to it:

   private static void GetAllTasksForUser(ClientContext context,string taskListName, string userLoginName)
        {
            using (context)
            {
                int count = 0;
                var user = context.Web.EnsureUser(userLoginName);
                context.Load(user, u => u.UserId);
                context.ExecuteQuery();
              
                    CamlQuery caml = new CamlQuery();
                  
                    caml.ViewXml = @"<View><Where><Eq><FieldRef Name='AssignedTo' LookupId='TRUE'/><Value Type='User'>" +
                                   user.Id+ "</Value></Eq></Where></View>";
                    ListItemCollection collListItems = context.Web.Lists.GetByTitle(taskListName).GetItems(caml);
                    context.Load(collListItems);
                    try
                    {
                        context.ExecuteQuery();
                    }
                    catch (Exception ex)
                    {
                        //todo : handle exception

                        Console.WriteLine(ex);
                    }

                    foreach (var item in collListItems)
                    {
                       //todo: do stuff

                    }
                   
                }
               

            }

Note:

1. Your question says that you want to get all tasks assigned to each user in a site. If the number of users are huge, then getting tasks for each of them individually is a bad idea.In that case, it is better to get all items in the tasks lists without any filter and apply an if condition in the for each loop.

2.The above caml filter will fail in case the task list contains more than 5000 items, in this case also it is better to get all items in the tasks lists using pagination and apply an if condition in the for each loop, or to index the AssignedTo column. Get Item in Batches

3. If there are multiple tasks lists and you want to find assigned tasks in all of them use SPSiteDataQuery (SSOM) or SearchAPI: SPSiteDataQuery