0

I have two custom objects in a master detail relationship where Proposal is parent and Question is child.

I need to create a page where all the questions in the DB are shown along with certain data elements from parent object and create a table e.g.:

Proposal Name     Proposal Type  Question Name Question Text

Normally in an SQL you can get that result with a query like below:

Select proposal_name, proposal_type, question_name, question_text 
from proposal, question 
where question.proposal_id = proposal.id

But I am not able to get the similar construct that I can use in APEX controller to fetch the desired set of data?

So far I have tried following combinations

[Select Proposals__r.Name, Proposals__r.Client_Name__c, Proposals__r.proposal_type__c, Question__c from Question__c];

Error:

Didn't understand relationship 'Proposals__r' in field path. If you are attempting to use a custom relationship, be sure to append the '__r' after the custom relationship name.

2nd Option:

[Select id, name, Client_Name__c, proposal_type__c, (select question__c from Questions__r) from proposals__c];

Result Query worked but not able to access questions data in visualforce page to display.

  • You should read this. Even after two years working with the platform, I routinely come back to it :) In this case, you'd query it as select name, proposal__r.name from question where proposal__c =: proposalIdVariable – dzh Jun 12 '15 at 3:40
  • I need to query all child record so I don't want to add where clause to it. Let me try your example without where clause – Neel Jun 12 '15 at 4:01
  • I used this [Select Proposals__r.Name, Proposals__r.Client_Name__c, Proposals__r.proposal_type__c, Question__c from Question__c]; and got following error: Didn't understand relationship 'Proposals__r' in field path – Neel Jun 12 '15 at 4:04
  • Looks like you've got your field names wrong. Can you post what are the lookup relationship field name? – dzh Jun 12 '15 at 4:58
  • Field Label Proposals Object Name Question Field Name proposal Data Type Master-Detail – Neel Jun 12 '15 at 5:12
0

There are 2 ways in which you can wirte SOQL queries.

1) We can get child object fields/ records by using inner query like this :

 [Select, Id, Name, Client_Name__c, Proposal_Type__c, (Select Id from Questions__r) from proposal__c];

2) OR

 [Select Question__c, Proposal__r.Name, Proposal__r.Client_Name__c, Proposal__r.proposal_type__c from Question__c];

// we dont use plural form of parent object while quering on the child object.

This will solve your problem. If you still have any issues regarding SOQL queries, follow this link https://developer.salesforce.com/docs/atlas.en-us.soql_sosl.meta/soql_sosl/sforce_api_calls_soql_relationships_query_using.htm#sforce_api_calls_soql_relationships_query_using

0

There could be multiple questions for each Proposal. And there could be multiple Proposals returned by your queries. So if there is list proplist storing all prosposals like this :

List<propsal> proplist = Select id, proposal_name, proposal_type, 
      (select id from Questions__r) from proposal__c here id = proposal.id

then for each proposal, you can get a list of questions like:

 list<question__C> queslist = proplist[0].questions__r

Specific to your example , you can try something like this:

<apex:dataTable value="{!proplist }" var="p">
        <apex:column value="{!p.ID}"/>
        <apex:column value="{!p.Name}"/>
        <apex:column>
         <apex:dataTable value="{!p.questions__r}" var="q">
            <apex:column value="{!q.id}"/>                          
            <apex:column value="{!q.name}"/>              
            <apex:column value="{!q.phone}"/>  
         </apex:dataTable>
        </apex:column>
</apex:dataTable>

I didn't test this. but I think this should work. Revert in case of any issue.

0

Following query might help (review field names)

Select id, proposal_name, proposal_type, 
(select id from Questions__r)
from proposal__c
where id = proposal.id

Refer to SOQL articles to get more details on relationship queries:- https://developer.salesforce.com/docs/atlas.en-us.soql_sosl.meta/soql_sosl/sforce_api_calls_soql_relationships_query_using.htm#sforce_api_calls_soql_relationships_query_using

Code to parse question

List<Question__c> lstQuestions = pr.Questions__r;
for(Question__c que : lstQuestions){
//access question here
}
  • Thanks this worked now I am not getting any error for query but how to I access question data in visual force page? If I try to use {!pr.question__C} I get an error – Neel Jun 12 '15 at 5:00
  • Question is a child of Proposal, so it can be accessed by parsing the list of questions associated with the Proposal. Updated code above. Also, if the solution has worked for you, please mark this as the correct answer. – Anshul Jun 12 '15 at 7:16
  • This solution also worked but I had a requirement to create a flat table with proposal data and this didn't allow me to do that. So, I am marking the answer that worked for me as correct one. Thanks for your suggestion also. – Neel Jun 13 '15 at 12:01
0

the solution to reference the "_r.fieldname" reference doesn't appear to work if you have a parent/child relationship and you're trying to use that relationship to access individual fields.

Is there any way to work around that limitation? Everything I have seen shows examples using nested soql statement which essentially creates nested lists in APEX and when rendered in a VF page it still shows 2 page block tables instead of one page block table with the combined data which is what I am looking for.

Anyone know how to workaround this?

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.