Using SPQuery to filter lists by User and Group Column in SharePoint 2007

Introduction

 If you want to filter a list based on the value in the look up field that is of type ‘Person or Group’, We have to consider the following options:

* Filter by the User Name.
* Filter by SPUser ID Property
* Filter by Domain User account

Filter by the User Name

By default the settings for the ‘Person or Group’ column will have the following settings.

When we run a SPQuery search on the list using the following code we will be able to filter the list based on the fullname of the user.

using (SPSite oSite = new SPSite("http://moss:80"))
{
using (SPWeb oWeb = oSite.OpenWeb())
{
oList = oWeb.Lists["Project Tasks"];
SPQuery query = new SPQuery();
query.Query = "Tushar Parikh";
SPListItemCollection items = oList.GetItems(query);
}
}

Filtering by SPUser ID

If you want to filter the list based on the SPUser ID then follow the steps below.

* Add an additional attribute ‘LookupId’ for the queried field in your CAML query

using (SPSite oSite = new SPSite("http://moss:80"))
{
using (SPWeb oWeb = oSite.OpenWeb())
{
oList = oWeb.Lists["Project Tasks"];

SPQuery query = new SPQuery();

query.Query = "7";

SPListItemCollection items = oList.GetItems(query);

}

}

Filtering by Domain User Account

If you want to filter the list based on the Domain User Account then follow the steps below.

* Change the ‘Show Field’ Settings of the Person or Group lookup column to ‘Account’

Modify your code to include the domain account in the filter value.

using (SPSite oSite = new SPSite("http://moss:80"))
{
using (SPWeb oWeb = oSite.OpenWeb())
{
oList = oWeb.Lists["Project Tasks"];
SPQuery query = new SPQuery();
query.Query = "komal\tparikh";
SPListItemCollection items = oList.GetItems(query);
}
}

Conclusion

I hope this is help to you all.