function readOnly(count){ }
Sign Up ›
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
Learn More

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
Kenji775Kenji775 

Somewhat Advanced SoQL Query Help (Subquery on a Child Object of a Parent Object)

Hello,

I am trying to write a query that will pull a few peices of information from several different tables. It basically is a child object, that queries for details on it's parent object, including getting information from another child object that is attached to that parent. So it goes up to the root, and back down another child. I can get it to read from the parent fine, however writting the subquery to the other child is proving problematic. Just for reference, it is orgniazed like this.

 

Respondent__c ----> Contact <----- Payments__c

                        |

                         ----> Campaign

 

Respondent is linked to Contact via the Respondent__r relational field, and Payments are linked to Contacts via the Contact__r relational field. Also, a respondent record is linked to two seperate campaigns, but that doesn't really matter for what I'm doing.

 

The error I get when I run my query is 

 

"INVALID_TYPE: Respondent__c.Respondent__r.Payment__r.Status__c From Respondent__c.Respondent__r.Payment__r) ^ ERROR at Row:6:Column:63 First SObject of a nested query must be a child of its outer query."

 

this is my Query

 

 SELECT ID,
Master_Campaign__r.ID,
Master_Campaign__r.name,
Respondent__r.firstname,
(Select Respondent__c.Respondent__r.Payment__r.Status__c From Respondent__c.Respondent__r.Payment__r),
Respondent__r.lastname,
Respondent__r.id,
Respondent__r.Organization1__c,
Respondent__r.Gender__c,
Respondent__r.email,
Respondent__r.Birthdate,
Respondent__r.PID__c,
Respondent__r.Phone,
Child_Campaign__r.ID,
Child_Campaign__r.Time_slot__c,
Respondent__c from Respondent__c  
where Child_Campaign__c = '701400000009Uu4'
order by Respondent__r.lastname

 

Here is a link to the pertinent sections of my WSDL.

http://portal.fpitesters.com/WSDL.html

 

Please let me know if you have any ideas. I just need to be able to select the payment information, If I remoev the sub query it works just fine, but I need that payment info. Otherwise I'm going to have to run another query to fetch it, which I really would rather not do. Thanks!

 

 

 

werewolfwerewolf

I'm not sure you can do what you want to do in a single query, but regardless, this doesn't look right to me:

 

(Select Respondent__c.Respondent__r.Payment__r.Status__c From Respondent__c.Respondent__r.Payment__r)

 

Respondent__c.Respondent__r?  Shouldn't that just be Respondent__r?

Kenji775Kenji775

You are probably right about removing that first peice. I had it without it originally, I was just testing that version and forgot to change back. I did try

 

 SELECT ID,
Master_Campaign__r.ID,
Master_Campaign__r.name,
Respondent__r.firstname,
(Select Respondent__r.Payment__r.Status__c From Respondent__r.Payment__r),
Respondent__r.lastname,
Respondent__r.id,
Respondent__r.Organization1__c,
Respondent__r.Gender__c,
Respondent__r.email,
Respondent__r.Birthdate,
Respondent__r.PID__c,
Respondent__r.Phone,
Child_Campaign__r.ID,
Child_Campaign__r.Time_slot__c,
Respondent__c from Respondent__c 
where Child_Campaign__c = '701400000009Uu4'
order by Respondent__r.lastname

 

And got the same error.

 

This quote from the docs make me think it is possible:

"In each specified relationship, no more than five levels can be specified in a child-to-parent relationship. For example, Contact.Account.Owner.FirstName

(three level)"

 

I just don't have the synax right or something. The docs I am referring to are:

http://www.salesforce.com/us/developer/docs/api/Content/sforce_api_calls_soql_relationships.htm

 

Thanks for the help so far.

 

Message Edited by Kenji775 on 03-05-2009 12:09 PM
SuperfellSuperfell
You can't go up then down again, you need to restructure your query so that the parent is the root of your query.
Kenji775Kenji775

Simon,

Okay, well thats good to know. I wasn't sure if I was just making an error or what. My problem with restructing though, is that if I start at the root (contact) and write sub queries for the child objects (respondent__c, and Payments__c), won't that return all contact records? I only want to select information for contacts where they have a respondent record for this study, and they don't need to have a payment record. Its like a left join kinda. Do you have any idea if that is possible or not? 

 

Basically I want to

 

Select

Contact.lastname,
Contact.id,
Contact.Organization1__c,
Contact.Gender__c,
Contact.email,
Contact.Birthdate,
Contact.PID__c,
Contact.Phone,

Contact.Payment__r.Status__c

Contact.Respondent__r.Master_Campaign__r.ID

Contact.Respondent__r.Master_Campaign__r.Name

Contact.Respondent__r.ID,
Contact.Respondent__r.Child_Campaign__r.Time_slot__c,
Contact.Respondent__r.Respondent__c from Respondent__c 

FROM Contact

Where There is a respondent record, but not neccessarily a payment record. 

 

Any help you can give would be appreciated. I've pretty much hit the wall of my SoQl skills with the above request. Thanks!

SuperfellSuperfell

You should be able to use the new semi-joins feature to filter the contact rows to the set you care about, something like

 

select .... from contact where id in (select contactId__c from respondent__c where ....)

 

the child object should use a nested select in the select list, e.g.

 

select last, id, ..., (select master_campaign__r.id, master_campaign__r.name from respondent__r) from contact where .... 
Farhat MirzaFarhat Mirza

 I have same issue

Can anybody help me in below mentioned issue.
I have Account as a master object with two child object(Alert and Customer).

Alert<--Account--->Customer

I want to query alert object and get some field from customer object using Account relation(Customer And Account has one to one relation)

My Query looks like
Select Subject,Account__r.Name,(Select Account__r.Id from Account__r... how to get info from customer object)
from Alert__c
Prakash@SFDCPrakash@SFDC

Hi All,

Even I have the same issue


I have Account as a master object with one child object(PartnerLocation) and Lookup(Oppurtunity).

PartnerLocation<--Account-----------Oppurtunity

I want to query Oppurtunity object and get some field from PartnerLocation object using Account relation(Oppurtunity  And Account has many to one relation)

My Query looks like


this.opps= SELECT Account.Id, Amount, Name, Account.Account_Subtype_M__c, StageName,  Primary_Technology__c,( select Account.Latitude__c, Account.Longitude__c from Opportunity.Account.Partner_Locations__r Limit 1), Account.BillingStreet, Account.BillingCity, Account.BillingCountry FROM Opportunity  WHERE Account.BillingCity<>\'\'

 

In the VF page  :

 

<apex:repeat value="{!opps}" var="opp"
            rendered="{!slcAnalysis=='Opportunity'}">
            <apex:repeat value="{!opp.Account.Partner_Locations__r}" var="PL"> 
            <script type="text/javascript">
                opps.push(["{!opp.Name}","{!opp.Account.BillingStreet}","{!opp.Account.BillingCity}","{!opp.Account.BillingCountry}","{!PL.Account.Latitude__c}","{!PL.Account.Longitude__c}","{!opp.Account.Id}","{!opp.Amount}","{!opp.Primary_Technology__c}"]);
            </script>
            </apex:repeat>
        </apex:repeat> 

 

here In VF page  opps.push() is a JavaScript function. with the above code i am getting an error like

"First SObject of a nested query must be a child of its outer query. " so how can i use the query result values in the page.

 

Thanks in Advance

Prakash.N

 

 

 

 
Farhat MirzaFarhat Mirza

As per my undertstanding in soql you can only retrieve parent object information from child object but not another child of that parent directly. In order to acheive this you need start querying from parent go to child .

In my case i was  Alert is child of  Account object And  CustomerInfo is a child of Account. I was having requirement to get cutomerInfo detials along alert data .

So my query looks like this

 

 for (Account accountObj: [Select Name,(SELECT Id, Name, Date__c, Comment__c, MRR__c,ASV__c,                                 Reason__c, Reason__r.name, Severity__c, Severity__r.name, Status__c, Status__r.name,Reason__r.SystemName__c,                                Status__r.SystemName__c, Type__c, Type__r.name, Type__r.SystemName__c, Severity__r.SystemName__c,                                NextStepDate__c,NextStepOwner__c,NextStepComment__c,                                LastModifiedDate, LastModifiedBy.Name                                FROM Alerts__r                                ORDER BY LastModifiedDate DESC LIMIT 10000),                                (Select NextRenewalDate__c from CustomersInfo__r)                                from Account acct Where acct.Id in(Select Account__c from Alert__c)                                ]        )

 

 

I hope this may help you.
Prakash@SFDCPrakash@SFDC

Thansk for your reply ,

 

In my case PartnerLocation is child to Account and oppurtunity is LookUp to Account.Can we combine both in one query ? I want to query oppurtunity throught which i want both Account and PartnerLocation values corresponding to the Oppurtunity .

 

 
Prakash@SFDCPrakash@SFDC

I Found the Solution . In these type of scenarios better to use Wrapper Class Concept. We can query the different objects and insert into a wrapper object , From wrapper object we can get  all the results . Curently i implemented this it is working fine to me.

 

Prakash