Soql Query on custom objects using junction object [duplicate]

Question

i have three custom objects.

Bank_Account__c(Parent) --->Bank_transaction__c(child)

Bank_Account__c(Parent) --->Bank_statement__c(child)

Bank_transaction__c fields are date__c, credit__c, debit__c

Bank_statement__c fields are start_date__c, end_date__c

I just want to write a single query to get the records of Bank_transaction__c which date__c is in between start_date__c and end_date__c of bank_statement__c object. How can i achieve this

Answer 1

As there could be hundreds of statements per bank account and tens of thousands of transactions per bank account I give a big +1 to the lookup field to relate the statements and transactions so later queries are then simple.

Fyi, you can query a parent and multiple child objects in a single SOQL request using relationship queries. (This is not the same thing as being able directly relate the date fields of the two child objects.) But without the relationship between Bank_transaction__c and Bank_statement__c, some careful code would be needed to avoid exponential cost trying to relate those objects via the dates:

for (Bank_Account__c a : [
        select name, ...,
                (select start_date__c, end_date__c from Bank_statements__r order by start_date__c),
                (select date__c, credit__c, debit__c from Bank_transactions__r order by date__c)
        from Bank_Account__c
        where ...
        order by name
        ]) {
    // Need a better algorithm than this to avoid the doubly nested loop.
    // E.g. a Map key with a date and a start/end date where if the
    // date in one key is within the start/end of another key they are equal.
    for (Bank_statement__c s : a.Bank_statements__r) {
        for (Bank_transaction__c t : a.Bank_transactions__r) {
            if (t.date__c >= s.start_date__c && t.date__c <= s.end_date__c) {
                // t belongs to s
            }
        }
    }
}