1

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.

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

  • @ezhilan This approach is for filtering from the pets. I read ur comment recently about not showing all the users i.e. you want o filter from the users. Then you need to implement it from the other way around – Prajith Feb 24 '16 at 11:10
  • Query on users -> Write a sub query on adoption in which you also bring in the parent pet detail field values and then use that result. – Prajith Feb 24 '16 at 11:11
  • Thanks for your detailed info. But if i use the query like you mentioned, then is shows all pet records but i want only pet records which have been requested for adoption. – Ean Feb 24 '16 at 11:23
  • @Ezhilan no probs, set up a rollup summary COUNT field on Pet details and then add a filter onto the query . WHERE FieldName__c > 0 – Prajith Feb 24 '16 at 11:25
  • Could you please update your query with the suggestion you mentioned in comment. That would really be helpful. – Ean Feb 24 '16 at 11:28
0

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

  • Yep. I tried your solution already but it doesn't help in my situation. Is it possible to write inner query from child to parent. – Ean Feb 24 '16 at 10:55
  • @Ezhilan no inner query from parent to child. – Tushar Sharma Feb 24 '16 at 11:23
0

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

  • Yep. I tried your solution already but it doesn't help in my situation. Is it possible to write inner query from child to parent. – Ean Feb 24 '16 at 10:56
  • Why it doesn't help ? can you elaborate on what is happening and what you are expecting ? – karthikselva Feb 24 '16 at 10:57
  • If I use the above query and retrieve the values then in grid it is showing as for each pet all users are being shown but only selected users should be shown. – Ean Feb 24 '16 at 11:03
  • can you define selected users ? is it a condition on Adoption__c ? – karthikselva Feb 24 '16 at 11:25

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.