5

I have an SObject called Team_Member__c

It has a lookup field Manager__c pointing to Team_Member__c, so the Team_Member__c SObject is related to itself. The relationship represents a management structure in an organization, where one team member may manage zero or more other team members.

I am trying to create a SOQL query to get a list of Team Members which do not manage anyone. I think I should be able to achieve this using a subquery and an aggregate function, but nothing I've tried has worked.

The only working solution I have so far is this:

List<Team_Member__c> tms = [select Manager__r.id from Team_Member__c];

List<Id> ids = new List<Id>();
for (Team_Member__c tm : tms) ids.add(tm.Manager__r.id);

List<Team_Member__c> drones = [select name from Team_Member__c where id not in :ids];

But I'm thinking there must be a way to do this with just one SOQL query?

2 Answers 2

3

It looks like if you are querying through a child relationship you can get it with just one query:

List<Team_Member__c> drones = new List<Team_Member__c>();
for (Team_Member__c tm : [select name, (SELECT Id FROM Team_Members__r /* Replace with the actual child relationship name*/) from Team_Member__c]){
    if (tm.Team_Members__r.size() == 0){
        drones.add(tm);
    }
}

You cannot do a query like this: (thanks highfive)

List<Team_Member__c> drones = [select name from Team_Member__c where id not in (select Manager__c from Team_Member__c)]; // invalid!!

The selected column in the subquery cannot be traverse relationships, so you might have run into an error if you had tried using Manager__r.Id instead of Manager__c in the subquery.

4
  • 1
    Does salesforce allow inner and outer selects on same object?
    – highfive
    Aug 20, 2015 at 10:24
  • @highfive no it does not. The SOQL reference had an example of it, and I just noticed that they said that it was invalid.
    – martin
    Aug 20, 2015 at 10:28
  • does that mean I'm stuck with the multiple-query solution I already have?
    – NickJ
    Aug 20, 2015 at 10:35
  • 1
    @NickJ It looks like you can do it if you query through the child relationship rather than the parent. If you replace the two Team_Members__r's in my edited post with the actual child relationship name, it should work.
    – martin
    Aug 20, 2015 at 10:52
2

Given it's a Hierachical Table (self referencing), the traditional Parent-Child SOQL won't be of much help to you. This is especially true since you can have n number of levels, where as the traditional parent-child is 1 (maybe 2) levels deep.

So, What I recommend will require only 1 SOQL query; however, you'll need to iterate over the results to determine which managers don't actually have Team Members referencing/reporting to them:

Map<ID, Team_Member__c> memMap = new Map<Id, Team_Member__c>();
Set<ID> mgrIds = new Set<ID>();
List<Team_Member__c> drones = new List<Team_Member__c>();


//query all members and manager Id...
//load to map for later reference and to store references to sObject records...
//then, add all Manager Ids to a Set<Id> to see which members are managers...

for(Team__Member__c member : [select Id, Name, Manager__c from Team_Member__c]){

    memMap.put(member.Id, member);
    mgrIds.add(member.Manager__c);  

}


//Now, loop over team_Member__c map collection, and see if they were a manager of a member by
//referencing the known managers obtained from prior FOR Loop...
for(Team_Member__ccheckMgr : memMap.values()){

    //essentially, if the Team_Member__c.Id is not a manager, then add to drone list...
    if(!mgrIds.contains(checkMgr.Id)){ 

        drones.add(checkMgr);

    }   

}

system.debug('# of Drones: ' + drones.size());

system.debug(Drones);

Note, this will hit governor limits if you have more than 50,000 team_Member__c records! Best option is to use answer posted before mine, or use 2 soql queries to exclude results.

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .