Search code examples
salesforceapex-code

soql getting data via a junction object

I have the following three custom objects:

Order__c

Design__c (has a lookup to Order and a lookup to Location, so the design ojbect is the junction object)

Location__c

On the order page layout I want to add a blank section that contains a VF page in order to display the Location records for all the design records for an order.

An order can have many designs and a design can have many locations. I need a way to group and display each design/locations in the VF page on the Order.

How can I build this query and display the results on the VF page?

I was trying a query like this: Select Location_r.Name, Location_r.Mockup From Design_c where Order_c = 'xxxxxxxxxxxxxx'

Also, is there a better way to display the results besides a VF page section in a related list?

Thanks for any help!

Regards.

Solution

  • First things, first. since design is related to the other 2 objects via lookup, it is not a junction object. junction objects are only when it's 2 parents have a master-detail relationship with the child.

    I think what you're trying to achieve is to traverse a parent-child-parent relationship. You'll need to use subqueries for the last half. From the documentation- here you go: http://www.salesforce.com/us/developer/docs/api/Content/sforce_api_calls_soql_relationships.htm

    Query **child-to-parent relationships**, which are often many-to-one. Specify these relationships directly in the SELECT, FROM, or WHERE clauses using the dot (.) operator.
For example:
SELECT Id, Name, Account.Name
FROM Contact 
WHERE Account.Industry = 'media'
This query returns the ID and name for only the contacts whose related account industry is media, and for each contact returned, the account name.




 Query **parent-to-child**, which are almost always one-to-many. Specify these relationships using a subquery (enclosed in parentheses), where the initial member of the FROM clause in the subquery is related to the initial member of the outer query FROM clause. Note that for subqueries, you should specify the plural name of the object as that is the name of the relationship for each object.
For example:
SELECT Name,
  (
    SELECT LastName
    FROM Contacts
  )
FROM Account
The query returns the name for all the accounts, and for each account, the last name of each contact.