Using SOSL to find all related records

Question

I am trying to write a query to find all child records of a parent record. I know I can do this with a SOQL on each child object, but I want to do something slicker.

For example, my parent object is called Parent_Object__c, and on every child Object, there is a lookup relationship aptly named Parent_Object__c.

I'd like a SOSL query similar to this:

id myRecordId='a09E000000NSo9K';

list<list<sObject>> myResult = [FIND :myRecordId
                               IN ALL FIELDS
                               RETURNING Child_Object_1__c, 
                                         Child_Object_2__c, 
                                         Child_Object_3__c]; 

But when I try this, I don't get any results. Does SOSL not search lookup relationships?

Answer 1

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.

Answer 2

No, SOSL only searches a small subset of fields, basically just text, email, name, and phone fields (not long text areas or picklists). Don't quote me on that list, for the life of me I couldn't find any documentation for what specific field types are searchable.

That said, if you want to search on an Id, you can create a mirror of it and search on that. Create a new text field to store the id, then have a workflow copy it over on create. With the Id nestled away in a text field it will be available to SOSL.

There are probably reasons not to do this. A couple that come to mind

• Delay between when a record is created/updated and when the fields are indexed
• Max result set size is 2K records