API
Version 7.x > API > How to query a user by a settings field and a role View modes: 
User avatar
Certified Developer v6
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

User avatar
Kentico Support
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

User avatar
Member
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

User avatar
Kentico Support
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