join three objects on remote action

Question

In regards to reports the regular ones are not working for what we are trying to achieve we have 5 objects that are related in someway or another but not specifically one to another, having an scenario like this

Object 1 has reference to 2 and 3
Object 2
Object 3
Object 4 has reference to 2
Object 5 has reference to 2

So in my initial query I can grab the needed info from Object 1, 2, 3 however since there is no connection from object 1,2 or 3 to objects 4 or 5 then I can't add them in the query or I don't know how to, now my questions is that once I pull the records I can run through them all and pull the needed related 4 and 5 object but how can I add that as part of the response, is there a way to create an array of objects or can I access objects 4,5 from the same query?

global static Object1__c[] getReport(Integer page, Id program){
    Object1__c[] rows = [
        select 
        Id,
        Name,
        /*Object2__r relations*/
        o1.Object2__c,
        o1.Object2__r.Name,
        o1.Object2__r.Id,
        /*Object2__r relations ends*/
        /*Object3__r relations*/
        o1.Object3__c,
        o1.Object3__r.Name,
        o1.Object3__r.Id,
        /*Object3__r relations end*/
        from Object1__c o1
        where
        o1.Program__c = :program
        limit 30
    ];
    if(rows.size() > 0){
        for(Object1__c o1:rows){
            /*query here the other two objects*/
            Object4__c[] o4 = [
                select 
                Id,
                Name
                from
                Object4__c
                where
                Object1__c = :o1.Object2__r.Id
            ];
            Object5__c[] o5 = [
                select 
                Id,
                Name
                from
                Object5__c
                where
                Object2__c = :o1.Object2__r.Id
            ];
            /* how do I add o4 and o5 to the o1 row? */
        }
    }
    return rows;
} 

Answer 1

You could write some code based on this Mock.makeRelationship code to wire up the disconnected objects after they are queried. Messy enough though as you have a total of 5 related objects. (Do read the whole blog post that this comes from too - Mocking SOQL sub-select query results by Andrew Fawcett)

An alternative is to create a simple Apex class to hold the parent-child relationships:

public class Relationship {
    public Object[] sob {get; private set;}
    public Relationship[] children {get; private set;}
    public Relationship(SObject sob) {
        this.sob = sob;
        this.children = new Relationship[] {};
    }
}

and accept that the serialised form will be changed:

[
    {
        "sob": ...,
        "children": [
            {
                "sob": ...,
                "children": [
                   ...
                ]
            },
            ...
        ]
    },
    ...
]

Note that you should bulkify your Object 4 and Object 5 queries too.

Answer 2

Tlacaelel, using Object 1 as the focal point, you can execute a single SOQL statement and pull data from all 5 objects. The trick to doing this is to leverage child subqueries in the statement.

For example, let's use the following analogies to your question:

• Object 1 = OpportunityContactRole has references to Opportunity and Contact through OpportunityContactRole.OpportunityId and OpportunityContactRole.ContactId
• Object 2 = Opportunity
• Object 3 = Contact
• Object 4 = Task has reference to Contact thorugh Task.WhoId
• Object 5 = Note has reference to Contact through Note.ParentId

Below is the Apex code which shows the single SOQL statement which pulls data from all five (5) objects.

// Get a list of all matching contacts, based on query criteria 
// established at the Opportunity Contact Role level
List<Contact> matchingContacts = [
    SELECT Id, Name,
        (
            SELECT Id,
                OpportunityId,
                Opportunity.Name
            FROM OpportunityContactRoles
        ),
        (
            SELECT Id, Subject
            FROM Tasks
        ),
        (
            SELECT Id, Title
            FROM Notes
        )
    FROM Contact
    WHERE Id IN (
        SELECT ContactId
        FROM OpportunityContactRole
        WHERE CreatedDate = THIS_WEEK
    )
];

// Go through all of the contacts, and systematically display the names
// of all related opportunities, the subjects of all related tasks, and
// the titles of all related notes.
for (Contact eachContact : matchingContacts) {

    // Display the names of all related opportunities
    for (OpportunityContactRole eachRole : eachContact.OpportunityContactRoles)
        System.debug(eachRole.Opportunity.Name);

    // Display the subjects of all related tasks
    for (Task eachTask : eachContact.Tasks)
        System.debug(eachTask.Subject);

    // Display the titles of all related notes
    for (Note eachNote : eachContact.Notes)
        System.debug(eachNote.Title);
}

You can see that the WHERE clause is applied at the OpportunityContactRole (i.e., Object 1) level, which is what you implied as a requirement in your question.