Apex Code Development You need to sign in to do that
Don't have an account?
Njanga 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
______________________
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
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.
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>
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:
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() ); }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
Could you provide your VF-Code and Controller here?
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>
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>Thank you so much Mast0r.
it has worked finally. Am so greatiful for your time and effort.