6

Can I find a string from all fields from all custom and standard objects and return the data (all field data) of all the records that matches for all the fields of those objects??

Also is there an option for fetching next 2000 record set?

Note :- If we create new objects later in Salesforce, our solution should handle that as well and should search in that new object and return it too. As of now I have 75 custom objects. I believe there would be at least about 50 to 75 Standard Salesforce objects additionally.

I have a requirement like this. I know that I need to use SOSL, I am confused as to how I can architect this.


Solution 1

Describe metadata and frame dynamic SOSL and execute the Dynamic SOSL in Apex and display data on VF Page using Pageblocktables or some light-weight html components.

Questions

  1. Am not sure about the limits on no. of objects that can be queried in one single SOSL call. Can someone give some information about that?

  2. Also if am retrieving from all objects, is there a restriction like I can retrieve only certain fields on all objects as results?

  3. What kind of limits will I be possibly hitting here? (ViewState? Heap size? what else?)


Solution 2

Do the same Dynamic SOSL but as batch apex and write the results to an excel file as an attachment to Attachment object or to ContentVersion object (Libraries) and send a link to the excel file as an email to the user who initiated it after the batch job is done.

Questions

  1. Again what would be the case if there are more than 2000 records as results across different objects?

Solution 3

Do the same as mentioned in Solution 2 but using Email Services as the heap size is 36MB.

Questions

  1. Again what would be the case if there are more than 2000 records as results across different objects?

Solution 4

Use Partner wsdl (SOAP API) in an external application (probably some .net application), provide results however they need, like in excel or UI with export to excel format.

Questions

  1. Not sure if I should use SOSL or SOQL here? (assuming there are more than 2000 search results)

  2. Are there any other better solution approaches that I can follow to address this requirement?

  • hey @Sathya, you never accepted the answer =P – glls Aug 2 '17 at 18:19
9

Solution 1:

Use @RemoteAction instead of standard rendering. You'll need JavaScript.

Example:

Controller:

public with sharing class searchall {
    @RemoteAction public static string[] searchableElements() {
        String[] terms = new String[0];
        for(Sobjecttype t:schema.getglobaldescribe().values())
            if(t.getdescribe().issearchable())
                terms.add(string.valueof(t));
        return terms;
    }
    @RemoteAction public static sobject[] searchQuery(string entity, string term) {
        return search.query('find :term in all fields returning '+entity)[0];
    }
}

Page:

<apex:page controller="searchall">
<script>
var searches = [], results = {}, term;
function startSearch() {
    searchall.searchableElements(elementResults);
}
function elementResults(result) {
    searches = result;
    term = searches.shift();
    doSearch();
}
function doSearch(records) {
    if(records) {
        results[term] = records;
        document.getElementById("output").innerHTML = records;
        if(searches.length) {
            term = searches.shift();
        } else {
            term = null;
        }
    }
    if(term) {
        document.getElementById("search").innerHTML = 'Searching '+term;
        searchall.searchQuery(term, document.getElementById('term').value, doSearch);
    }
}
</script>
<input type="text" id="term"/>
<button onclick="startSearch()">Search</button>
<pre id="search"></pre>
<pre id="output"></pre>
</apex:page>

You'll be limited to 2000 records per entity. You should probably introduce an artifical delay to avoid banging the server to the point where salesforce.com asks you to stop. Note that you could actually search up to 20 objects per iteration; the 2000 record limit is per-search, not per-transaction (at least, as far as the docs appear to state). View state is not used here at all, and since it's all JSON, it will load and render faster than pure Visualforce. You can query the the additional fields using a SOQL after you have the record list. NOTE This isn't meant to be a great example, just a proof of concept.

Solution 2:

Use an Iterable to collect 2000 records per entity.

Example:

global class FullDBSearch implements Database.Batchable<String>, Iterator<String>, Iterable<String>, Database.Stateful {
    string[] backlog;
    string term;
    sobject[] allresults;
    integer counter;

    global FullDBSearch(string term) {
        backlog = new string[0];
        for(sobjecttype t:schema.getglobaldescribe().values())
            if(t.getdescribe().issearchable())
                backlog.add(string.valueof(t));
        this.term = term;
        allresults = new sobject[0];
        counter = 0;
    }
    global boolean hasnext() {
        return !backlog.isempty();
    }
    global iterable<string> start(database.batchablecontext bc) {
        return this;
    }
    global iterator<string> iterator() {
        return this;
    }
    global string next() {
        return backlog.remove(0);
    }
    global void execute(database.batchablecontext bc, string[] scope) {
        while(!scope.isempty()) {
            sobject[] records = search.query('find :term in all fields returning '+scope.remove(0))[0];
            allresults.addall(records);
            counter++;
        }

    }
    global void finish(database.batchablecontext bc) {
        system.debug(logginglevel.error, allresults.size()+' records processed');
        system.debug(logginglevel.error, counter+' objects processed');
    }
}

Note that this has to be invoked with a maximum batch size of 20:

Database.executeBatch(batchInstance,20);

I wouldn't actually store the list of records, if you can help it. Just store the list of ID values, which you can query for values in your finish() method, or you can store the list of ID values in a custom object so you can retrieve the list later, or you can even compose an email. You're still limited to 2,000 records per entity. This is a common, unavoidable theme. You can probably get away with querying the records in the finish function as long as you have less than 50,000 records and less than 100 total objects involved.

Solution 3:

This is the least accessible solution, because you only get 20 searches to perform. I would avoid this route entirely. It's simply not viable for maximizing your search results.

Solution 4:

This requires much more code than either 1 or 2, and you'll run into a heavy API usage limit as opposed to batch or Visualforce. As a SOSL, you'd still be limited to 2000 records per search, and SOQL may not help you, since the generated SOQL could quickly reach the 20,000 character limit, complexity limit, or non-selective limits.

  • Excellent!!! @sfdcfox thanks for such a detailed answer!! I will continue further and will let you know if am able to achieve it through Solution 1 or 2. – Sathya Aug 8 '13 at 16:47

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.