+ Start a Discussion
Zaheer KhanZaheer Khan 

How to join multiple objects using SOQL, Apex, or ....?

Hi,

I need to join four objects for my Visualforce page.
 
I am trying to get customer info, ordered products, and invoiced amount. For example Customer “John” ordered product “ABC” and invoiced amount is “$1200”. In order to get this done, I need to join four objects. Two objects are custom objects and two standard objects.       
 
Objects are: Account, Invoice__c, Invoice_Line__c, Product2
 
Account à Invoice__c à Invoice_Line__c
                   Product2    à Invoice_Line__c
 
Invoice__c object has Account__c lookup column to Account.Id column for relationship
 
Invoice_Line__c object has Invoice__c lookup column to Invoice__c.Id column for relationship
 
Invoice_Line__c object has Product__c lookup column to Product2.Id column for relationship
 
 
I need to merge these four objects to get the following information:
 
Account.Customer_Id__c, Account.Name from Account object
 
Invoice__c.Invoice_Id__c, Invoice__c.Invoice_Date__c from Invoice__c object
 
Invoice_Line__c.Brand__c, Invoice_Line__c.Name, Invoice_Line__c.Net_Amount_US__c from Invoic_Line__c object
 
Product2.ID__c, Product2.Brand__c, Product2.Description__c, Product2.Family, Product2.Product_Line__c, Product2.Product_Series__c from Product2 object
 
I am new to SOQL and Visualforce. What is the best way to get it done? Your help will be appreciated.
 
Best Answer chosen by Zaheer Khan
ManojSankaranManojSankaran
Hi Zaheer,

You have created a lookup field in Invoice Line for Product2. You have to use that api name. for example

if the lookup field name is product__c then the query will be like (Kindly check the lookup field api name)
Product__r.id,Product__r.Brand__c


If this solves your query. Mark it as answer.



Thanks
Manoj S
 

All Answers

ManojSankaranManojSankaran
Hi Zaheer,

This is my understanding about your hierarchy
1. Account 
2. Invoice
3. Invoice Line Item

Product is not realted to account and invoice. we always have to start the query from the lowest child object. In our case its Invoice Line.

This is the sample query

Select id,Brand__c ---------------- (Invoice Line Related Fields)
Product2.id,Product2.Brand__c, ------------ (Products related to that invoice Line)
Invoice__r.id,Invoice__r.Invoice_Date__c, ----- (Invoice related to Invoice Line)
Invoice__r.Account__r.id, Invoice__r.Account__r.Name ---- (Acconut related to Invoice Line's Invoice)

u can add more fields as you wish. 
Please mark this as answer if it helps.


Thanks
Manoj S

 
Zaheer KhanZaheer Khan
Hi Manoj,

Thanks for your reply. 

Query is working fine with linked objects but generates error when I include Product2 object. For example, as you said start from child object, so Invoice Line >> Invoice >> Account join works but when I include Product2 object, Invoice Line >> Product2 >> Invoice >> Account, it generates an error.

Please see code below (it works fine without Product2 object):
Select id, Brand__c, Invoice__r.id,Invoice__r.Invoice_Date__c, Invoice__r.Account__r.id, Invoice__r.Account__r.Name
From Invoice_Line__c

But the following code genrates error (with Product2 object)
Select id, Brand__c, Product2.Id, Product2.ID__c, Invoice__r.id,Invoice__r.Invoice_Date__c, Invoice__r.Account__r.id, Invoice__r.Account__r.Name
From Invoice_Line__c   

Error messege says "INVALID FIELDS: Select id, Brand__c, Product2.Id, ..... Did not understand relationship 'Product2' in field path..................."

Look forward to hear from you soon.

Zaheer Khan
ManojSankaranManojSankaran
Hi Zaheer,

You have created a lookup field in Invoice Line for Product2. You have to use that api name. for example

if the lookup field name is product__c then the query will be like (Kindly check the lookup field api name)
Product__r.id,Product__r.Brand__c


If this solves your query. Mark it as answer.



Thanks
Manoj S
 
This was selected as the best answer
Zaheer KhanZaheer Khan
Hi Manoj,

Thanks for your help. SOQL query is working fine now. As you suggested, api name was not right. 

Thank you so much

Zaheer