+ Start a Discussion
NjangaNjanga 

Subquery

Hi guys,

Am  stuck here and I really need your help.

I have two custom objects Jobs__c and Shortlists__c.

Jobs__c is the Parent object and Shortlists__c is the child object.

 

Each Job can have many shortlists. Now what I want is to write a query will retrieve Job Name with corresponding number of shortlists

 

 

E.g.

 

______________________
Job Name       No.Shortlists
______________________
J2ee                 10           

______________________

Best Answer chosen by Admin (Salesforce Developers) 
mast0rmast0r

Sorry, i cant find any error at your code? :(

 

I would try it some easier:

 

public class JobsATSController{

    public List<Job_ATS__c> getJobs(){
        List<Job_ATS__c> j = [Select id, Name, Job_Name__c, (Select Id From Shortlists__r) FROM Job_ATS__c ];
        return j;
    }
}

<apex:pageBlockTable value="{!Jobs}" var="j">
    <apex:column value="{!j.Name}"/>
    <apex:column value="{!j.Shortlists__r.size}"/>
</apex:pageblocktable>

 

All Answers

priyanka.mv26priyanka.mv26

To retreive the id, name from both the objects, write the following query.. Add the field name to the query  if need to retreive other fields

 

select id,name,(select id,name from Shortlists__r) from Jobs__c 

 

The above query is written assuming the child relationship name as "Shortlists__r". Please change it with the corresponding child relationship name otherwise it will not work.

mast0rmast0r

I would say, try to use this:

 

List<Jobs__c> jobs = [ Select Id, Name, (Select Id From Shortlists__r) From Jobs__c Limit 10 ];

<apex:pageBlockTable value="{!jobs}" var="j">
    <apex:column value="{!j.Name}"/>
<apex:column headerValue="Shortlists">
{!j.Shortlists__r.size}
</apex:column> <apex:pageBlockTable>

 

 

 

NjangaNjanga

Thank you all for quick reply;

 

let me explain further about my scenario; No.Shortlists shoud be the count for shortlists under that Job.

E.g. J2ee has 20 shortlisted candidates;

 

I tried to use this query;

 

List<Jobs__c> Jobs = [Select id,Name,Job_Name__c,
    (Select Count()  From Shortlists__r )
     FROM Job_ATS__c];

but am getting this error:

ErrorError: Compile Error: COUNT() can only be used with root queries

 

mast0rmast0r

You don't need to count the child relationship, the subquery takes automatically all related records and this is a list. So count it just with size():

 

List<Jobs__c> Jobs = [Select id, Name, (Select Id From Shortlists__r) FROM Job_ATS__c];

for(Jobs__c j :Jobs){
    Systrem.debug('Number of children: ' + j.Shortlists__r.size() );
}

 

NjangaNjanga

Thank you so much Mast0r.

I have tried to logger the result just as you have demonstrated and its working fine but when i try on vf its bringing this Error:

 

SObject row was retrieved via SOQL without querying the requested field: Jobs__c.Shortlists__r

mast0rmast0r

Could you provide your VF-Code and Controller here?

NjangaNjanga

 

public class JobsATSController{

public List<Job_ATS__c> allJobs { get;set;}

 

public JobsATSController(){
        loadData();
         for(Job_ATS__c j:loadData()){
         System.debug(j.Name +'       Number of Shortlists: ' + j.Shortlists__r.size());
     }

}

 

public List<Job_ATS__c> loadData(){

return allJobs = [Select  id, Name, Job_Name__c, (Select Id From Shortlists__r) FROM Job_ATS__c ];

}

 

}

 

 

VF Page:

 

 <apex:page showHeader="true" sidebar="true" controller="JobsATSController">
     <apex:sectionHeader title="JOB ATS" subtitle="JOB ATS"/>
     <apex:form id="form">
 
    <apex:pageMessages id="errors" />
          <apex:pageblock title="ALL JOBS" >
 
                <apex:pageBlock mode="edit">
                 <left> <apex:commandButton value="New Job" id="newJob" action="                        {!URLFOR($Action.Job_ATS__c.new)}"      />         

    </left>
                </apex:pageBlock>
                           <apex:pageBlockTable value="{!allJobs}" var="j">
                                        <apex:column value="{!j.Name}"/>
                                        <apex:column value ="{!j.Shortlists__r.size}"/>
                                  </apex:pageblocktable>
                    
                            </apex:pageblock>
                    </apex:form>
</apex:page>

mast0rmast0r

Sorry, i cant find any error at your code? :(

 

I would try it some easier:

 

public class JobsATSController{

    public List<Job_ATS__c> getJobs(){
        List<Job_ATS__c> j = [Select id, Name, Job_Name__c, (Select Id From Shortlists__r) FROM Job_ATS__c ];
        return j;
    }
}

<apex:pageBlockTable value="{!Jobs}" var="j">
    <apex:column value="{!j.Name}"/>
    <apex:column value="{!j.Shortlists__r.size}"/>
</apex:pageblocktable>

 

This was selected as the best answer
NjangaNjanga

Thank you so much Mast0r.

it has worked finally. Am so greatiful for your time and effort.