function readOnly(count){ }
Sign Up ›
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
Learn More

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
Mark Z DeanMark Z Dean 

Querying UserRole Object

I ran simple query below and it gave me results: 
select Id, name, userrole.name  from user
The child relationship name is called 'Users' on the User object so I was initially the query below but it kept erroring out: 
select Id, name, users__r.name  from user
I thought every call to a parent must have '__r' appended but not in this case plus it doesn't even use the relationship name i.e. users. Can someone explain why the first query worked and the second one didn't...Many thanks...





 
Raj VakatiRaj Vakati
try this 
  
Select Id ,Alias, UserRoleId, UserRole.Id, UserRole.Name from User

 
Raj VakatiRaj Vakati
SELECT Id, Name, DeveloperName 
FROM UserRole 
WHERE Id NOT IN (SELECT UserRoleId 
                 FROM User 
                 WHERE UserRoleId !='000000000000000')

 
Mark Z DeanMark Z Dean
Thanks rajamohan but I don't need a query, I figured that out myself...I am looking for answers to the 2 questions I asked so I can get more clarity... Sent from my Samsung Galaxy smartphone.
Mark Z DeanMark Z Dean
Can anyone help clarify the questions I have. Thanks...
Gaurav KhandekarGaurav Khandekar
@Mark in your first query "User" is the child object and "User Role" is the parent so when you query on User you can refernce parent fields. A parent can multiple childs but a child will have only parent.

While in your second Query "Users" is Child and "User" is parent. So the reason why your query is failing because you are running a query on "Parent (User)" and refercncing "Child (Users)" fields which will eventually fail as the system is not able to determine which child to refer to as technically there can be multiple of them. If you want to try, go ahead and query on "Users (child)" and try referncing "User (parent)" object, it will work.

Hope this helps you! 
Mark Z DeanMark Z Dean
Makes sense...But in the first query, shouldn't  
userrole.name

be called as below? I thought every relationshop call should have a '__r' appended to it? 
userrole__r.name
Ajay K DubediAjay K Dubedi
Hi Mark,

Below Query can fulfill your requirements, Hope this will work for you.

1. [Select Id,Name,UserRole.Name from user]

Even you can use below one to see username and role on the same row 
 
2. [Select Id,Name,UserName,UserRole.Name from user]

Please mark this as best answer if this solves your problem.

Thank you,
Ajay Dubedi
Shashank JaiswalShashank Jaiswal
Hi Mark,

I am little late to the party but the reason is a custom relationship has '__r' suffixed but standard does not.

For example, take the relationship you were exploring with UserRole. Similarly, if you were to access fields of Profile object from within the User query, you can do so by simply using "Profile.Id" or "Profile.Name".

Same principal applies when you want to access a field of Account object from Contact.
e.g. 
SELECT Id, Name, Account.BillingPostalCode FROM Contact
However, when it comes to custom relationships, '__r' is used for querying relationship.
 e.g. 
SELECT Id, Name, Account__r.Name from Project__c

Hope this helps.