0

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;
} 
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.

| improve this answer | |
  • This Mock.makeRelationship is interesting. But I am not quite sure about what is the difference between this and regular child subqueries? – Lance Shi Dec 4 '14 at 23:45
  • @LanceShi Its using the fact that JSON deserialize can handle deep graphs of related objects and correctly set the __r fields in the process, where as SOQL is more limited e.g. one level of children only. More devious than elegant though. – Keith C Dec 4 '14 at 23:48
1

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.

| improve this answer | |
  • This is a good thought and these sort of re-arrangements are worth looking into. But in this case, I don't think it works. For example, if there are no Contact rows present your query will return no results. In the query in the question, if there are no Object 3 rows present, Object 1 rows can still be returned. That is they are not equivalent queries. – Keith C Dec 4 '14 at 23:18
  • I am getting a "Didn't understand relationship" I don't think this works or maybe I am writing it wrong, for the moment I think I will go with the @KeithC Object[] approach and then I will further try your query – Tlacaelel Ramon Luis Dec 5 '14 at 21:52

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.