Sign Up ›

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
hemmhemm 

SOQL to get a My Team's Query

From what I understand, there is no easy way in SOQL to do a My Team's query.  A "My" query is a simple OwnerId = :UserInfo.getUserId(), but nothing this simple for My Team's.

 

Can anyone suggest a logical way to build SOQL that mimics the concept of My Team's?

mtbclimbermtbclimber

Check out semi-joins in the API doc (scroll down). They'll do what you want. Here's an example from the doc which should illustrate the concept:

 

 

SELECT Id, Name 
 FROM Account 
 WHERE Id IN 
(SELECT AccountId FROM Opportunity WHERE StageName = 'Closed Lost')

 

 
hemmhemm

I am familiar with that feature.  

 

However, my question is more about where to look in Salesforce to determine who is on "My Team".  In Reporting, a My Team's query will look for all data owned by anyone below you in the role hierarchy.  With companies having loads of hierarchy levels and users, an approach to iterate through every role and come up with a list of user Ids could a) take too many script statements or b) make the query string too long. 

 

What I am looking for is a way to figure this out dynamically and reliably no matter how big a hierarchy a company has or how many users they have.

 

What really should happen is that SOQL should get some keywords to do this for me, but in the meantime I want to see if I can figure this out.
mtbclimbermtbclimber

Ahh, misinterpreted "Team" sorry.

 

Nope. No way to do this reliably. SOQL will return all records you can see according to sharing (when WITH SHARING is employed) but  there is no way to apply any sort of filter that dynamically applies an "where owner is under me in the role hierarchy" mechanism. Good idea though ;-)
TehNrdTehNrd

I think you'd have to determine the user's Role, traverse down the role hierarchy, query all active user's in these roles and and User Ids to a set (userIds), and then finally do your query with an IN :userIds.

 

Similar to this: http://blog.jeffdouglas.com/2011/02/15/find-my-salesforce-users-by-role-hierarchy/
hemmhemm

Yeah, that's what I didn't want to have to do.  I was hoping there was an easy way to determine who is in the Roles & Subordinates Group like you can see in the UI, but no such luck.
AndyHilliardAndyHilliard

It'd be interesting to know how it's implemented in Salesforce. Since it's computationally-intensive, and doesn't change often, I'd guess it's cached somewhere (in a who-can-see-what kind of data structure).