Sign Up ›

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

Subquery in Select field list

I am stuck with this query that is supposed to pull a count of related objects with every object.

 

My data model is that I have an "Assignment" object that has a lookup field that points to a "Resource" object.  The Resource object doesn't have any fields pointing to Assignments.  

 

From the documentation, it seems like my query should look like this:

 

resources = [ SELECT Name,
              Primary_Discipline__c,
              Project_Manager__c,
              Account_Executive__c,
              Foreign_Languages__c,
              (SELECT count() FROM SFDC_Assignment__r)
             FROM SFDC_Resource__c
          ];

 

The error message I get is:

 

Error: Compile Error: Didn't understand relationship 'SFDC_Assignment__r' in FROM part of query call. If you are attempting to use a custom relationship, be sure to append the '__r' after the custom relationship name. Please reference your WSDL or the describe call for the appropriate names. at line 6 column 23

 

Any ideas?

 

Thanks,

 

Seth

sggottliebsggottlieb

I made some headway by using the internal name of the relationship:

 

 

resources = [ SELECT Name,
              Primary_Discipline__c,
              Project_Manager__c,
              Account_Executive__c,
              Foreign_Languages__c,
              (SELECT count() FROM R00N40000001HoCZEA0)
             
             FROM SFDC_Resource__c
          ];
 
I am not happy with this solution because it is difficult to read and understand but I can live with it until something more elegant comes along. 
 
 
Now I am stuck with another issue...  Apparently I can't use count() in a subquery.  The documentation I found said to use a "Roll-Up Summary" field.  I can't do this because I am not using Lookup relationship rather than a Master-Detail Relationship.  The information block says "You cannot create this type of field on this object because it is not the master in a master-detail relationship."
 
Suggestions?
 
Thanks,
 
Seth 
 


sandersensandersen
Since you're in Apex code, why not change your subquery to get all the rows? then you could loop though and count them in your code. I think that's the easiest way.
 
Thanks,
 
Steve 


sggottliebsggottlieb
Thanks for the idea @sandersen. 
 
Are you recommending that I create another custom controller (or some other class or component) with a method that would count assignments and do other queries if I pass in a Resource ID?  Then call that method in every iteration of my loop? Or is there a way to inject another calculated field on the dataset that I pass back from my controller? 
 
Wouldn't it be inefficient to do a join at the code level?  There must be some way to do this at the query level.   
 
Thanks,
 
Seth 


sandersensandersen
I was thinking something like this:
 
resources = [ SELECT Name,               Primary_Discipline__c,              Project_Manager__c,              Account_Executive__c,              Foreign_Languages__c,              (SELECT Id FROM SFDC_Assignment__r)             FROM SFDC_Resource__c          ]; for (SFDC_Resource__c myResource : resources) {
	Integer numberOfAssignments=0; 	numberOfAssignments  = myResource.SFDC_Assignment__r.size();
	//do something with the number 
} 
 
The syntax isn't right, I'm sure, but that's the gist. You can do a subquery of related items and then get the size of that query result and do whatever you want with it.
 
Thanks,
 
Steve 
 
 


sggottliebsggottlieb
Thanks!  How do I make that number of assignments visible on the dataset that I expose to my Apex Page?
 
<table border="1">
      <tr>
          <th>Name</th>
          <th>Discipline</th>
          <th>PM</th>
          <th>Account Exec</th>
          <th>Foreign Languages</th>
          <th>#Number Projects</th>
          <th>Regions Worked</th>
      
      </tr>
  <apex:repeat value="{!resources}" var="r">
      
      <tr>
          <td>{!r.Name}</td>
          <td>{!r.Primary_Discipline__c}</td>
          <td>{!r.Project_Manager__c}</td>
          <td>{!r.Account_Executive__c}</td>
          <td>{!r.Foreign_Languages__c}</td>
          <td>
              <!--WHAT TO DO I PUT HERE?  -->         
          </td>
          <td></td>
      </tr>
  </apex:repeat>
  </table>
 
 
 


sggottliebsggottlieb
Maybe a bigger question I should be asking is why the Assignments relationship is not visible through the report designer and why I can't access this relationship in an Apex query with SFDC_Assignment__r.  I am using the Services Project Manger app:  http://sites.force.com/appexchange/listingDetail?listingId=a0N300000016aZXEAY
 
Thanks,
 
Seth 


sggottliebsggottlieb
For anyone else struggling with this problem, I got it working although I am not at all pleased with the implementation.  
 
I created a new Class called ResourceRow:
 
 
public class ResourceRow {
    public final SFDC_Resource__c resource;
    public final Integer assignmentCount;
    public final String regions;
    
    public ResourceRow (SFDC_Resource__c r, Integer ac, String regs) {
        resource = r;
        assignmentCount = ac;
        regions = regs;
    }
    
    public SFDC_Resource__c getResource() {
        return resource;
    }
    
    public Integer getAssignmentCount(){
        return assignmentCount;
    }
    
    public String getRegions(){
        return regions;
    }
    
    
}
	
 
 Then I created some additional methods to do the sub queries.  Here is the controller class
 
 
public class StaffingReportController {

      private final List<SFDC_Resource__c> resources;

      public StaffingReportController() {
          resources = [ SELECT Name, 
              Primary_Discipline__c,
              Project_Manager__c,
              Account_Executive__c,
              Foreign_Languages__c
              
             FROM SFDC_Resource__c
          ];
      }

      
      public List<ResourceRow> getResources() {
          
          List<ResourceRow> rows = new List<ResourceRow>();
          for (SFDC_Resource__c resource : resources) {
              ResourceRow rr = new ResourceRow(resource, getAssignments(resource.Id).size(),getRegions(resource.Id));
              rows.add(rr);
          }
          
          return rows;
      }
      
      public  List<SFDC_Assignment__c> getAssignments(String r) {
          Integer count = 0;
          String qry = 'SELECT Projects__c FROM SFDC_Assignment__c WHERE Resource__c = \''+r+'\'';
          return Database.query(qry);
          
      }
      
      public String getRegions(String r) {
          List<SFDC_Assignment__c> assignments = getAssignments(r);
          String regionString = '';
          for (SFDC_Assignment__c assignment : assignments) {
              String qry = 'SELECT Region__c FROM SFDC_Projects__c WHERE Id = \''+assignment.Projects__c+'\'';
              List<SFDC_Projects__c> projects = Database.query(qry);
              
              for (SFDC_Projects__c project : projects) {
                  regionString = regionString + ' ' + project.Region__c;
              }
              
          }
          return regionString;
          
          
      }
      
      
}

 
One of the nastier elements of this code is that I need to call the same query (to get the assignments) twice.  Blech.  This will work for my protype but I am hoping that someone can propose a more elegant solution.  Note: the relationships "__r" don't seem to work as advertised.
 
 


rajarakrajarak
You can try this 
 
resources = [ SELECT Name, 
              Primary_Discipline__c,
              Project_Manager__c,
              Account_Executive__c,
              Foreign_Languages__c,
              (SELECT count() FROM SFDC_Assignments__r)
             FROM SFDC_Resource__c
          ];
Or, check the name of this object in the 'Child Relationship' option from app explorer.
I'm sure it should be (select count() from SFDC_Assisgnments__r) or the watever name specified in the child relationships.
 
Its working for me. 


Ken KoellnerKen Koellner
Something that through my on sub-queries with custom objects is that SF pluralized the relationship name.
 
I had a custom object, we'll call it "My_Custom_Object, related to Contact and the relationship name was "My_Custom_Objects__r", not "My_Custom_Object__r".
 
-K