query flat list of records including parent and child fields

Question

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.

Answer 1

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

Answer 2

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.

Answer 3

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
}

Answer 4

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?