^ Click Here

Friday, February 17, 2012

SOQL - Learning the parent-child queries for Salesforce 1

Well... Lots of time the need arises to show all the opportunities for one contact ( or similar parent child relationships )

Considering it, there are two major part of this first is the query part where we would call all the opportunities for the contacts
 and second is the concept of wrapper class which is actually the mapping of two or more entities.

In salesforce to have a join in a query you must have a parent-child relationship....it's pretty easy to understand that one parent can have many childs.

So Actually there can be two ways of writing a query

(i) Make the child as the central object of the query.
     ex. - [SELECT NAME, Contact.Account.NAME , Contact.Account.somefield FROM Contact];

(ii)Make the parent as the central object of the query.
     ex. - [SELECT NAME, somefield, (SELECT NAME FROM Account.Contacts) FROM Account];

Both the queries above will retrieve same data but in different format and both are used according to the need or requirements (ex - For which entity sorting etc. is needed).

Noticeable things are
  1.  while referring to parent the dot(.) notation is used (contact.account.name in first query).
  2.  while referring to child a subquery is used with the fields it is retrieving .
  3.  in second query the child subquery consist Account.Contacts (and not Account.Contact). Actually here the Contacts does not represent a object type instead it represents the relationship between the Account object type and Contact Object type.
Hence one important thing to note is that while referring to a child subquery we must use the name of the relationship and not the object.

P.S you can get the name of the relationship from looking into the 'wsdl' file (type="tns:queryResult" represent child relationship name
to be used in subquery).

Things which wouldn't work :

a> [SELECT * FROM Contact] . There is nothing like '*' in soql, you have to write the fieldnames you want to retrieve.

b> [SELECT id, name, (select name from Account.Contact where createdDate < 2010-01-01), (select name from Account.Contact where createdDate > 2010-12-31) FROM Account]
    You can't use two subqueries for same child relationship while retrieving.

c> [SELECT name,(select name,(select name from Opportunities) from Contacts) FROM Account], the subquery inside another subquery i.e. nested subqueries are not allowed.

Due to these reason you would have to frame the query according to your need.

This should be enough for the start, We would dwell into parent-child relationship query more in the next part.

No comments:

Post a Comment