function readOnly(count){ }
Sign Up ›
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
Learn More

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
steven75steven75 

Query - How to join different Objects (table)

Hi,

 

I am new to Apex and SOQL, and  trying to learn programing.   What I have is this code below in the class

 

public class opportunityList2Con {
    public ApexPages.StandardSetController setCon {get {
            if(setCon == null) {setCon = new ApexPages.StandardSetController(Database.getQueryLocator([select name, Amount, ExpectedRevenue, closedate from Opportunity]));
            }
            return setCon;
        }set;
    }
    public List<Opportunity> getOpportunities() {
         return (List<Opportunity>) setCon.getRecords();
    }
}

 Then this code in the page Test1:

<apex:page controller="opportunityList2Con">
    <apex:pageBlock >
        <apex:pageBlockTable value="{!opportunities}" var="o">
            <apex:column value="{!o.name}"/>
            <apex:column value="{!o.Amount}"/>
            <apex:column value="{!o.ExpectedRevenue}"/>
            <apex:column value="{!o.closedate}"/>
        </apex:pageBlockTable>
    </apex:pageBlock>
</apex:page>

 So this is pulling a list of Opportunities with some fields.  However, I would like to edit a query to JOIN with other objects CASES and TASKS to pull any cases or tasks/events that associate with the opportunity, but I couldn't figure out the query.   so example I would like to so the Join tables like this in MSSQL or Oracle8.  

 

Select from op.name, op.amount, c.number, c.subject, a.subject, a.AssignedTo

From Opportunity op, Case c, a.Activity

Where

op.caseID = c.caseID

c.ActivityID = a.ActivityID.

 

This query example is bad, but I want to use it so you would know what i am trying to accomplish.

 

Any advice would be appreciated,

 

Thanks,

mtbclimbermtbclimber

Check out the documentation on SOQL Relationships. Basically you need to traverse across a defined relationship. I don't know of any standard one that joins case with opportunity so you'll need to apply this to however that is defined in your account but for activities, say "Task", you'd do something like this to aggregate the tasks for each opportunity in your list:

 

 

[select name, Amount, ExpectedRevenue, closedate, (select subject from Tasks) from Opportunity]

 

 

Then in your page you can iterate over the tasks like this:

 

 

<apex:page controller="opportunityList2Con">
    <apex:pageBlock >
        <apex:pageBlockTable value="{!opportunities}" var="o">
            <apex:column value="{!o.name}"/>
            <apex:column value="{!o.Amount}"/>
            <apex:column value="{!o.ExpectedRevenue}"/>
            <apex:column value="{!o.closedate}"/>
            <apex:column headerValue="Tasks">
                <apex:dataList value="{!o.tasks}" var="t">
                    {!t.subject}
                </apex:dataList>
            </apex:column>
        </apex:pageBlockTable>
    </apex:pageBlock>
</apex:page>

 

 

 
steven75steven75

This is really helpful,  Thank you.
steven75steven75

Another question.


This query below is working fine.  However, I don't understand this. 

I see the standard object of the task is "Task" (without a "s"), but this query has "select subject from Tasks" which works fine.   I tried to change to Task (without a "s" char), then it won't work.   Please advise the different.  

 

[select name, Amount, ExpectedRevenue, closedate, (select subject from Tasks) from Opportunity]

 

Thanks,
Akshaya MashankarAkshaya Mashankar
Hi 

Task is generally used for the Workflow tasks when assigned, approved etc. <My guess>

However a short hand query would be >
  
select name, Amount, ExpectedRevenue, closedate, Tasks.subject from Opportunity

Thanks Akx