Apex Advanced Selector Queries

Sub Queries

public Contact getRelatedDetails(Id userId){
       fflib_QueryFactory qf = new fflib_QueryFactory(Contact.sObjectType);
       qf.selectField('name').selectField('Id').setCondition('Id In (Select ContactId from User where Id=:userId)');
       qf.subselectQuery('Goals__r').selectField('Id');
       qf.subselectQuery('Account_Client__r').selectField('Id').selectField('External_Id__c').selectField('Institution_Account__c').setCondition('Status__c=\'Active\'');
       Contact bpContact = Database.query(qf.toSOQL());
       return bpContact;
     }

Pagination with Ordering

  public List<Goal__c> selectByUserIdAndPageNumber(Id userId, Integer pageNumber){
     Integer calcOffset = pageNumber * 200;
     fflib_QueryFactory goalQueryFactory = newQueryFactory();
     fflib_QueryFactory.Ordering ordering = new fflib_QueryFactory.Ordering(Goal__c.CreatedDate, fflib_QueryFactory.SortOrder.DESCENDING, false);
     goalQueryFactory.addOrdering(ordering);
     List<fflib_QueryFactory.Ordering> orderings = goalQueryFactory.getOrderings();
     orderings.remove(0);
     String queryWithOffSet = goalQueryFactory.setCondition('Client__c in (Select ContactId from User where Id=:userId)').setLimit(maxGoalsPerPage).toSOQL() + ' offset ' + calcOffset;
     return (List<Goal__c>) Database.query(queryWithOffSet);
}

Aggregate Query

public List<AggregateResult> getAggregateDetails(List<Id> securityIds){
    String selectedACLevel = 'Level_1__c';

    String formatQuery = String.format('Select {0} sumOfAllocPercent, {1} from Classification__c where Security__c IN :securityIds Group By {2}', new String[]{'sum(Asset_Percent__c)', selectedACLevel, selectedACLevel});
    List<AggregateResult> aggregateResultForAC= (List<AggregateResult>) Database.query(formatQuery);

    return aggregateResultForAC;
}