apex-enterprise-patterns / fflib-apex-common

Common Apex Library supporting Apex Enterprise Patterns and much more!
BSD 3-Clause "New" or "Revised" License
913 stars 517 forks source link

How to add subquery filter in fflib_SObjectSelector? #213

Closed pranayjswl007 closed 5 years ago

pranayjswl007 commented 5 years ago

I have a custom object(Residence__c) which has lookup relationship to Account, Now I am trying to use FinancialForce sObjectSelector to Querry Account with Residences and have few filters on residences.

I created the first Selector for Residences

public class ResidenceSelector extends fflib_SObjectSelector {
        public Schema.SObjectType getSObjectType() {
            return Residence__c.sObjectType;
        }

        public List<Schema.SObjectField> getSObjectFieldList() {
            return new List<Schema.SObjectField>{
                    Residence__c.Name,
                    Residence__c.Id
            };
        }
    }

Now creating a Selector for Account

public class AccountSelector extends fflib_SObjectSelector {
        public Schema.SObjectType getSObjectType() {
            return Account.sObjectType;
        }

        public List<Schema.SObjectField> getSObjectFieldList() {
            return new List<Schema.SObjectField>{
                   Account.FirstName,
                   Account.LastName,
                   Account.PersonBirthdate

            };
        }

        public List<Account> selectById(Set<ID> idSet) {
            return (List<Account>) selectSObjectsById(idSet);
        }

        public List<Account> selectByIdWithCurrentResidences(Set<Id> idSet){
            // Query Factory for this Selector (Account)
            fflib_QueryFactory accountQueryFactory = newQueryFactory();
            accountQueryFactory.setCondition('Id IN :idSet');          

            //Add a query sub-select via the Query Factory for the Residences

            ResidenceSelector rs = new ResidenceSelector();

            fflib_QueryFactory residenceQueryFactory = rs.newQueryFactory(false);
            residenceQueryFactory.setCondition('Is_Current__c = true');
            residenceQueryFactory.setLimit(1);

            rs.addQueryFactorySubselect(accountQueryFactory, 'Residences__r'); //Residences__r is the relationship name

            System.debug(accountQueryFactory.toSOQL());
            return (List<Account>) Database.query( accountQueryFactory.toSOQL() );

        }

    }

As you can see in my residenceQueryFactory which I got from ResidenceSelector I am adding Limit and conditions. And as its a subquery I added it to parent accountQueryFactory.

Generated SOQL:

SELECT FirstName, LastName, PersonBirthdate, (SELECT Id, Name FROM Residences__r ORDER BY Name ASC NULLS FIRST ) FROM Account WHERE Id IN :idSet ORDER BY Name ASC NULLS FIRST I expected the subquery filter and Limits to be applied on subquery, but they are not as seen in System.debug, Can someone point me what bit I am doing wrong?

cropredyHelix commented 5 years ago

Try

public List<Account> selectByIdWithCurrentResidences(Set<Id> idSet){
            // Query Factory for this Selector (Account)
            fflib_QueryFactory accountQueryFactory = newQueryFactory();
            accountQueryFactory.setCondition('Id IN :idSet');          

            fflib_QueryFactory residenceQueryFactory = 
               accountQueryFactory.subSelectQuery('Residences__r')
                 .setCondition('Is_Current__c = true')
                .setLimit(1);

    return Database.query( accountQueryFactory.toSOQL() );
}