Forums (Obsolete)


Certified Developer v6	Dave - 2/1/2013 12:15:46 PM How to query a user by a settings field and a role Hi, I'd like to use the API to find users within a certain role that have a specific value for a custom field (in the User Settings table). Is this easy to accomplish? Thanks, Dave

Kentico Support

kentico_zdenekc - 2/4/2013 5:32:07 AM

RE:How to query a user by a settings field and a role

Hi,

This would be probably best to do with using either a custom query or the general User select method. The key thing is that you need to filter out the Users that aren't members of the searched Role, which involves using an inner SELECT inside the Where condition.
In the API, you can use the method:

UserInfoProvider.GetUsers(where, orderBy)

the where Where condition would be something like:

UserID IN (SELECT UserID FROM CMS_UserRole 
           WHERE RoleID = <id of the searched Role>) 
AND UserCustomField = <searched value>

The GetUsers method internally selects all the fields in the CMS_User and CMS_UserSettings tables (connected in a View), unless you specify a limited set of columns by the Columns property of the three-argument GetUsers method overload.

Regards,
Zdenek


Member	csnyder-wte - 4/19/2013 2:40:57 PM RE:How to query a user by a settings field and a role CMS_usersetting is NOT being included in the query according to the error I get when attempting to select using a custom field in V7. SELECT * FROM CMS_User WHERE ReassignEligible=1

Kentico Support

kentico_zdenekc - 4/25/2013 5:55:34 AM

RE:How to query a user by a settings field and a role

Hi,

Where do you use this query? If it's run alone, it of course doesn't select the fields of the CMS_UserSettings table, but passing it to the GetUsers method should...

If you're running the query alone in the code, you could use the View_CMS_User instead of CMS_User table, as this View joins both User and UserSettings tables (including custom fields).

Regards,
Zdenek