[SalesForce] Find all records referencing a specific Account

Lookup fields are indexed for purposes of executing queries faster, but they are not search-indexed, so you cannot perform a search using a record ID in lookup fields. I do agree that this would be a handy way to leverage indexing to retrieve multiple children objects simultaneously. You will indeed need to use SOQL in order to retrieve the children.

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.