1

I have two object A__c(s1__c) and B__c(s2__c) and relationship among them is Master-details. I have to make a SOSL search in field of both(s1__c and s2__c).

Find {searchText} Returning A__c(s1__c), B__c(s2__c)

But this query result separate search on both objects. Is it possible to make search in child object and return search of parent as well.

I have also tried this.

Find {searchText} Returning  B__c(s2__c, AId__r.s1__c)

This query doesn't search on object A.

The reason I want this because I have to apply pagination on child object result.

2

We cannot use relationship field in SOSL query (SELECT clause, though in WHERE condition it supports).

So, better take this approach, first search in child object and take the ParentIds and then create a wrapper class with the data.

In the below example, I am searching on case comments based on AccountId and then storing Case Comments and Case information in wrapper.

public void searchString()
    {
        List<CaseCommentWrapper> lstWrapper = new List<CaseCommentWrapper>();

        List<CaseComment> lstComment = [FIND :searchString
                            IN ALL FIELDS RETURNING CaseComment (id,ParentId,
                            CommentBody
                            WHERE ParentId = :AccountId
                            ORDER BY CreatedDate DESC)                         
                            LIMIT 1000][0];

        //perform validations
        if(lstComment.size()>0)
        {
            showResult = true;

            Map<Id,Id> commentToCaseIdMap = new Map<Id,Id>(); //commentId, CaseId
            for(CaseComment cont : lstComment )
            {
                commentToCaseIdMap.put(cont.Id,cont.ParentId); //commentId, CaseId
                CaseCommentWrapper objWrapper = new CaseCommentWrapper();
                objWrapper.cont = cont;
                objWrapper.Id = cont.Id;
                objWrapper.ParentId = cont.ParentId;
                objWrapper.CommentBody = cont.CommentBody;
                lstWrapper.add(objWrapper);
            }

            //=============================

            Map<Id, Case> mapCaseCommentQuery = new Map<Id, Case>([SELECT Id, CaseNumber
                                                        FROM Case
                                                        WHERE Id IN:commentToCaseIdMap.values()]);
            system.debug('@@@lstWrapper'+lstWrapper);
            for(CaseCommentWrapper wrapper:lstWrapper)
            {   
                system.debug('@@@lstWrapper'+lstWrapper);
                if(mapCaseCommentQuery.containsKey(wrapper.ParentId))
                {
                    String strCaseNumber = mapCaseCommentQuery.get(wrapper.ParentId).CaseNumber;                        
                    wrapper.CaseNumber = strCaseNumber; 
                }         
            }
            lstSetController = lstWrapper;
        }
        else
        {
            showResult = false;
            ApexPages.addmessage(new ApexPages.message(ApexPages.severity.INFO,'No Records Found.'));
        }

    }

    public class CaseCommentWrapper
    {
        public CaseComment cont{get;set;} 
        public String Id {get;set;}
        public String ParentId {get;set;}
        public String CaseNumber {get;set;}
        public String CommentBody {get;set;}

        public CaseCommentWrapper()
        {       
        }
    }

Finally, display lstSetController in JQuery datatable for pagination.

  • I will post this at my blog on this Saturday. – Santanu Boral Mar 15 '18 at 20:07
  • Thank you for detailed answer. But how would I apply pagination on this using OFFSET & LIMIT. – Manish Mar 15 '18 at 20:13
  • Put LIMIT 1000, as list supports max 1000 records – Santanu Boral Mar 15 '18 at 20:14
  • dont need offset, just put the lstSetController in JQuery datatable, it will take accordingly, refer my post for some datatable related code – Santanu Boral Mar 15 '18 at 20:15
  • it is for rest API. – Manish Mar 15 '18 at 20:21
0

I have implemented through making search on parent object first then do SOQL to get all related child of parents and then check if we need to more records to return if yes, then search again on child object and exclude result of SOQL query.

Find {searchText} Returning A__c(Id)

Collect Ids of parent from this search e.g parentIdList.

Select Id, AId__r.S1__c, S2__c From B__c Where AId__c in :parentIdList Limit :pageSize offset :offset

then check if we need to return more records of B__c. If number of records are less than the page size then make this search with conditons.

Find {searchText} Returning B__c(Id, AId__r.S1__c, S2__c where AId__c not in :parentIdList Limit :remaining offset :offset)

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.