Apex Code Development You need to sign in to do that
Don't have an account?
Njanga SOQL
Hi Guys,
Can someone help me out here!
This is my situation:
I have two custom objects Job_ATS__c and Shortlists__c.
Job_ATS__c is the Parent and Shortlists__c is the Child. The relationship is one to many.
That means one job can have many shortlists and each shortlist has a Status__c. Status are 'Interested' or 'Not Interested'
So what I want is a query that will return Job Name and count for Interested and Not Interested shortlists.
E.g...
Job_ATS__c Shortlists__c Desired Result
______________ __________________________________ ______________________________
Job Name id Shortlist Name id job_id Status Job Name Interested Not Interested
______________ __________________________________ _______________________________
Job1 1 Shortlist1 1 1 Interested Job1 1 1
Job2 2 Shortlist2 2 1 Not Interested Job2 1 0
Shortlist3 3 2 interested
I need to Display the desired result on Vf page.
That's because you have taken only one map. I think you should be querying the Job records such as:
List<Job__c> lstJobs = [Select Name, Id From Job__c Where Id IN : interestedRecords.keySet() AND Id IN : notInterestedRecords.keySet()];
Now you have a collection of those Job records.
Now on your page, inside your pageblocktable, iterate through this list instead of the interested map.
<apex:pageBlockTable value="{!lstJobs}" var="j">
<apex:column headerValue="Job Name">
{!j.Name}
</apex:column>
<apex:column headerValue="Interested">
{!interestedRecords[j.Id]}
</apex:column>
<apex:column headerValue="Not Interested">
{!notInterestedRecords[j.Id]}
</apex:column>
</apex:pageBlockTable>
All Answers
You can easily do it with apex. But it would be even more simpler if you create two Roll up fields on Job_ATS__c, one with the filter for "Interested" status and other for "Not Interested" status. Then you can directly display those two fields on your visualforce.
If that doesn't work for you for some reasons, then this code should help you:
// take two maps, one will store the count for Interested records and other for Not Interested records
map<Id, Integer> interestedRecords = new map<Id, Integer>();
map<Id, Integer> notInterestedRecords = new map<Id, Integer>();
/// query shortlists record, check with the field names in the query, and add any filters you need
for(Shortlists__c s : [Select Job__c , Status__c From Shortlists__c Where "in case you have any filters"])
{
// for interested records
if(s.Status__c == 'Interested')
{
if(!interestedRecords.containsKey(s.Job__c))
{
interestedRecords.put(s.Job__c, 1);
}
else
{
interestedRecords.put(s.Job__c, interestedRecords.get(s.Job__c) + 1);
}
}
// for not interested records
else if(s.Status__c == 'Not Interested')
{
if(!notInterestedRecords.containsKey(s.Job__c))
{
notInterestedRecords.put(s.Job__c, 1);
}
else
{
notInterestedRecords.put(s.Job__c, notInterestedRecords.get(s.Job__c) + 1);
}
}
}
You can use this map to get "Interested" and "Not interested" records on your visualforce.
Thank you For quick reply.
I have tried it and its working. the big challege is to display both maps.
I have tried with Interested and i can display Job Name and Interested Shortlists but i cant display corresponding Not Interested Shortlists.
This is my Vf Code:
<apex:pageBlockTable value="{!interestedRecords}" var="interested">
<apex:column headerValue="Job Name">
{!interested}
</apex:column>
<apex:column headerValue="Interested">
{!interestedRecords[interested]}
</apex:column>
</apex:pageBlockTable>
That's because you have taken only one map. I think you should be querying the Job records such as:
List<Job__c> lstJobs = [Select Name, Id From Job__c Where Id IN : interestedRecords.keySet() AND Id IN : notInterestedRecords.keySet()];
Now you have a collection of those Job records.
Now on your page, inside your pageblocktable, iterate through this list instead of the interested map.
<apex:pageBlockTable value="{!lstJobs}" var="j">
<apex:column headerValue="Job Name">
{!j.Name}
</apex:column>
<apex:column headerValue="Interested">
{!interestedRecords[j.Id]}
</apex:column>
<apex:column headerValue="Not Interested">
{!notInterestedRecords[j.Id]}
</apex:column>
</apex:pageBlockTable>
Thank you so much for your time and effort. it has finally worked.