How can I make this SOQL query with Apex variable?

Question

I have these objects and relationships in my org:

Contract__c >- Account -< IL__c

Where >- and -< mean many-to-one/one-to-many relationships. So Account can have many Contract__c's and many IL__c's. Only one of those Contract__c has an active__c check field set to true.

I need to query IL__c with a where clause comparing IL__c.start__c to a field in Contract__c, but that field varies according to some conditions in that Contract__c.

Contract__c has these date fields:

• start_a__c
• end_a__c
• start_b__c
• end_b__c

If end_a__c is null, I have to query IL__c's where start__c >= Contract__c.start_a__c. Otherwise, I have to query where start__c >= Contract__c.start_b__c. If the "a" period (the one defined by start_a__c and end_a__c) is closed, it doesn't make sense for the process to retrieve those records.

So my idea was to build a Map. Key is the Account id. The value is in instance of SomeClass, which has an instance of the active Contract__c of the Account. And SomeClass has someMethod() which, by examining the Contract__c conditions I previously explained, returns the correct start to compare with. And I intended to use that map like this:

select start__c, end__c
from IL__c
where start__c >= :theMap.get(accountId__c).someMethod()

Being accountId__c the relationship of the IL__c with the Account. It fails to save my class because "variable accountId__c does not exist".

How can I query the exact needed set of IL__c records and nothing else?

Answer 1

This could be a good approach

• Figure out the date you need
• Query the records

You are not saying if you have to get many accounts at the same time or just one. I will, for now, assume this is for only one.

public List<IL__c> getILs(Contract__c cont) {
    //Figure out the right date
    Date myDate = (cont.end_a__c == null) ? cont.start_a__c : cont.start_b__c;

    //Correct the field names for the account... I just assumed
    List<IL_c> theReturnedRecs = [select start__c, end__c 
                            from    IL__c
                            where   start__c >= :myDate
                            and     Account__c = :cont.accountId__c];

    return theReturnedRecs;

}

If you need to retrieve from multiple accounts at once, let me know and I will rewrite this.

Answer 2

If I understand what you want to do correctly, you're just passing the accountId into a method as a string, so only need a placeholder variable for it. If that's the case, you simply need something like what's below. Just add the rest of the code to hold theMap and someMethod(). You didn't say if you were doing this from VisualForce so I didn't add getters or setters.

public class MyQuery{

public string acctId;

public MyQueryLst<IL__c>;

public static list<Il__c> getMyQueryLst(string acctId){

    MyQueryLst<IL__c> = [select start__c, end__c
       FROM IL__c
       WHERE start__c >= :theMap.get(accountId__c).someMethod()];

       return MyQueryLst;
}

} 

Answer 3

I'm trying to read between the lines here, treating your compilation error as a symptom instead of the root cause. Further, I assume that this will be used in a trigger (or called in a trigger context). If you're using this in a Visualforce controller or controller extension, my solution is overkill.

What you're looking to accomplish can't be done with just a single query. The solution I'm proposing will only consume a single SOQL query (technically 1 of the 100 SOQL query limit + 2 of the 300 aggregate query limit), but there's more work that needs to be done (outside of the query) to get what you're looking for.

Functionally speaking, there's no difference between including a statement in the WHERE clause and using an if/else inside of a loop. Ok, yes, the if/else in a loop will cause more rows to be returned and use more of the CPU time limit but, that aside, the end result is the same.

So, we use a query to gather the initial dataset, and then loop over it to refine.

Parent-child subqueries will help us out here. We can use them to keep everything tied to the correct account.

Map<Id, Account> initialData = [SELECT Id, 
    (SELECT Id, start_a__c, end_a__c, start_b__c, end_b__c FROM Contracts__r WHERE isActive = true), 
    (SELECT Id, start__c FROM ILs__r) 
    FROM Account];

You can do the work of finding your (hopefully) single Contract record in the initial query because it doesn't depend on anything except its own fields.

From there, loop and refine

Date workingDate;
Contract__c workingContract;
Map<Id, List<IL__c>> accountIdToILResults = new Map<Id, List<IL__c>>();
for(Account acct :initialData){
    workingContract = acct.Contracts__r[0];

    // Basic error checking. Go to the next iteration of the loop
    // if, for some reason, there isn't a contract
    if(workingContract == null || acct.Contracts__r.size() > 1){
        continue;
    }

    // Below is my best interpretation of your required logic.
    // Change as needed
    if(workingContract.start_a__c != null && workingContract.end_a__c == null){
        workingDate = workingContract.start_a__c;
    } else if (workingContract.start_b__c != null && workingContract.end_b__c == null){
        workingDate = workingContract.start_b__c;
    } else {
        continue;
    }

    // Now that we have the appropriate date, loop over the IL__c records, and filter accordingly
    for(IL__c workingIL :acct.ILs__r){
        if(workingIL.start__c <= workingDate){
            if(!accountIdToILResults.containsKey(acct.Id)){
                accountIdToILResults.put(acct.Id, new List<IL__c>());
            }
            accountIdToILResults.get(acct.Id).add(workingIL);
        }
    }
}

If you don't care about having the IL__c records keyed on Account Id (which would be helpful if this is executed as part of an Account trigger), then you can simply build a list instead.