Query between Opportunity and Account

Question

I'm a new in this salesforce world.

I have to show Owner | Account Name | Opportunity Name | Last Meeting Date | Amount

I created an Apex Class and VisualForce Page. This is my query

public List<Opportunity> getOpportunity() {
    List<Opportunity> opp = [SELECT o.id, o.ownerid, o.name, o.AccountId, o.Type, o.Amount from Opportunity o, o.Account a where ownerid =: UserInfo.getUserId()];

    return opp;
}

With this query I have a problem, I don't know where to get a Last Meeting Date.

I think I should create a join between Opportunity and Account. Can someone help me with this...

Answer 1

You will want to add a join to your current query. It should look something like the following.

public List<Opportunity> getOpportunity() {
List<Opportunity> opp = [SELECT o.id, o.ownerid, o.name, o.AccountId, o.Type, o.Amount o.Account.Last_Meeting_Date__c FROM Opportunity o WHERE ownerid =: UserInfo.getUserId()];

return opp;
}

Answer 2

Queries in Salesforce are a bit weird if you have experience with standard SQL (or pretty much any rdbms that isn't Salesforce, for that matter).

Salesforce's SOQL doesn't have joins per se. That is, there is no JOIN keyword (and you can only specify a single object in the WHERE clause). If you're querying the Opportunity object, and need a field on the Account object, you simply follow the relationship using dot notation. You also don't need to alias the object that you're querying for.

[SELECT Id, Account.Name FROM Opportunity WHERE <condition>]

In your case, the full query would likely be

[SELECT Id, OwnerId, Account.Name, Account.Last_Meeting_Date__c, Name, Amount
FROM Opportunity 
WHERE OwnerId = :UserInfo.getUserId()]

The only sticky bit here is to recognize that the relationship name that you use in the dot-notation in the SELECT clause depends on whether the relationship is a standard field (which is the case between Opportunity and Account) or a custom field (which ends in __c).

For most standard relationships, the API name of the field is <related object name>Id such as AccountId on Opportunity. When fetching Account fields in an Opportunity query, you drop the 'Id' from the field's API name.

For custom relationships, where the field name ends in __c, you simply change that to __r.

That's for traversing upwards in a relationship hierarchy (ie. from a child record to it's parent record), and you can traverse upwards up to 5 levels.

Traversing down a hierarchy is another matter, and you can only traverse one level down.

The developer documentation on relationship queries goes into more detail, and should be helpful.

Answer 3

If you want to access a field of the account, it´s not need to perform a join, you can acces fields of a lookup:

SELECT o.Account.Last_Meeting_Date__c  FROM Opportunity o WHERE ownerid =: UserInfo.getUserId()