I am wondering if it is possible to query a record with 2 inner queries of the same related list with different filter.

for exemple :

    public static Account getAccountByCustomerNumber(string customerNumber){
    Account account = [SELECT id, Status__c, Person_Account_Id__c, Account_Number__c, 
                         openOrders(SELECT id FROM Orders WHERE Status != 'Closed'),
                         closedOrders(SELECT id from Orders where Status = 'Closed')
                       FROM Account
                       WHERE Account_Number__c = :customerNumber
                       Limit 1];
    return account;

This is not working but there is an other way to do that ?


Tested the core of the query that you provided:

SELECT id, (SELECT id FROM Orders WHERE Status != 'Closed'), (SELECT id from Orders where Status = 'Closed') FROM Account

And the error message is clear

"ERROR at Row:1:Column:78 Cannot follow the same aggregate relationship twice: Orders"

I would advice you quering all orders with their Status field and then filtering them on the front end assuming you need them for a Lighning component or make two methods returning Account with Closed and Opened orders.

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.