How to write child to parent relationship within a query

Question

I think maybe someone asked the same type of question already but still i am not able to find the exact answer I am looking for.

I am having 3 custom objects namely

• User__c
• Pet_Details__c
• Adoption__c.

The adoption table has 2 master-detail lookup relationship fields and 1 unique adoption id field and 1 date field.

(These two below fields are like child-parent relationship type)

1.Pet_Id__c (Master table is Pet_Details__c)

2.User_Id__c (Master table is User__c)

Now I need to use a select query something like this,

 Select Pet_Name__c(from Pet_Details__c table),
         (Select UserName,Phone,Email(All these from User__c table) 
    FROM Adoption__c where Adoption_Date__c != null

I need this in above format so that I can display a parent-child grid like (For each pet how many users have have requested for adoption). Any help would be appreciated.

Answer 1

If your objective is finding how many users have requested for adoption of a pet, you could query from the Parent object(Pet) to the child(Adoption) using a sub query and in the sub query you can refer to the User parent's fields and fetch those info.

Lets assume that the child relationship name of Adoption is Adoptions__r. This can be found from the setting page of the field 'Pet_Id__c'. Lets assume that there exists a rollup summary field named NoOfAdoptions__c in Pet_Details__c object that counts the number of child adoption records present under it - https://help.salesforce.com/apex/HTViewHelpDoc?id=fields_defining_summary_fields.htm Lets assume that you are interested in the Pet_Name__c field(column) only in the Pet table(object) Lets assume that you are interested in UserName, Phone and Email fields on the User table(object)

So query from the Pets object, FILTERING out only those Pet_Details__c records which have adoption records under them, or order etc are left to your discretion.

SELECT id,Pet_Name__c FROM Pet_Details__c WHERE NoOfAdoptions__c > 0

Now lets get all the child adoption records associated with each of these Pet_Details__c records. For this we use a sub query - NOTE that I used the child relationship name for adoption

SELECT id,Pet_Name__c,(SELECT id,.User_Id__c FROM Adoptions__r) FROM Pet_Details__c WHERE NoOfAdoptions__c > 0

Now this query returns you a list of Pet_Details__c i.e List. Now each member of the list is a Pet_Details__c record and it gives the following 3 info:(lets access the first rec in the list>

1. Listname.get(0).id -> the ID of the Pet_Details__c record
2. Listname.get(0).Pet_Name__c -> the Pet_Name__c of the Pet_Details__c record
3. Listname.get(0).Adoptions__r -> gives you a List that are the children of the particular Pet_Details__c record. In this list as of now 2 fields are present - ID and User_Id__c

Now lets expand the sub query and query out the parent user values too.

SELECT id,Pet_Name__c,(SELECT id,.User_Id__c,User_Id__r.UserName,User_Id__r.Email,User_Id__r.Phone  FROM Adoptions__r) FROM Pet_Details__c WHERE NoOfAdoptions__c > 0

Now the query is complete. The query returns a List and each element of this list contains another List, which consists of the child adoption records under that pet detail record. And this list contains user info for each of the adoption record too.

Hope this helps

Answer 2

Write your query like

Select Pet_Id__r.Pet_Name__c,
       User__r.UserName,
       User__r.Phone,User__r.Email 
 FROM Adoption__c where Adoption_Date__c != null

If you refer parent object field then you need __r instead of __c. And if you want record from child object then use inner query. Select Id, (Select id from contacts) from account.

Reference

Answer 3

You need to use Pet_Detail__r and User__r for joining with master tables (similar to joins in SOQL)

Try this query:

Select Pet_Details__r.Pet_Name__c,User__r.UserName,
User__r.Phone,User__r.Email From Adoption__c

Refer this for complex joins in SOQL