function readOnly(count){ }
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
+ 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