How do I SOSL across objects, matching all records with FIND clause?

Question

To all the expert SOSL gurus lurking out there, I thought this will be an interesting one:

I have some SObjects of different types and I want to return their Name values in a single query.

In advance, I know the IDs and the ObjectTypes of the records whose Names I wish to retrieve. It seems 'wrong' to make several SOQL queries even though there aren't too many types.

So being a good platform citizen, I'm using Dynamic SOSL to retrieve what I need in a single query:

List<List<SObject>> sobjectsLists = Search.query(
    'FIND {*?*} RETURNING ' +
    'CustomOne__c (Name WHERE Id IN (\'a01000000000000AAA\')),' +
    'CustomTwo__c (Name WHERE Id IN (\'a02000000000000AAB\')),' +
    'CustomThr__c (Name WHERE Id IN (\'a03000000000000AAC\'))'
);
System.debug(sobjectsLists);

But there is one problem: I must specify at least one FIND character and {*?*} doesn't cut it.

System.SearchException search term must be longer than one character: ?

I've tried this to no avail: {*a* OR *b* OR *c* OR *d* OR *e* OR ...} but even if that did work, it wouldn't help in the case that some twisted cowboy puts underscore (_) or god forbid umlaut (ä).

Any guidance on how I can get the Name of all these records at once?

Answer 1

create a field on your object called Id__c

make a workflow rule with field update that copies Id across to Id__c

[
    FIND 'a01000000000001 OR a02000000000002 OR a03000000000003 OR ...' RETURNING
    CustomOne__c (Name),
    CustomTwo__c (Name),
    CustomThr__c (Name)
]

caveats:

• search is case insensitive
• this work around would make use of the use of the metadata api to create workflow rule, field update, custom field etc

Answer 2

Found a way to do this in SOSL but with major caveats: the clauses in RETURNING are applied after the search, so if the result limits are reached, the records won't be found. (courtesy superfell)

Trick is to satisfy the FIND clause with '* OR *' and use the WHERE clause to do the selecting:

[
    FIND '* OR *' RETURNING
    Account (Name WHERE Id = '0012400000DFDp0'),
    Contact (Name WHERE Id = '0032400000Ar4as'),
    Partner (Name WHERE Id = '00I2400000Ar4as'),
    Herp__c (Name WHERE Id = 'a0124000000yn22'),
    Derp__c (Name WHERE Id = 'a0224000000n3ll'),
    Doop__c (Name WHERE Id = 'a032400000001ps')
]

It gives the Id and the Name from any number of objects, costing a single SOSL query.

Query string can be dynamically assembled, using Id.getSObjectType to get the API names.

Answer 3

That's an abuse of SOSL; you're trying to hack around a critically missing piece in Apex Code: retrieve(). Retrieve isn't available in Apex Code (and for goodness' sake, why NOT?!). SOQL is the only appropriate way to retrieve records by ID, for now.

Answer 4

If you could "promise" that each record has at least 1 related Task / Event maybe you could pull this ... stunt... by querying Event with Polymorphic SOQL enabled...

Last example in TYPEOF docs looks promising

SELECT 
  TYPEOF What
    WHEN Account THEN Phone, NumberOfEmployees
    WHEN Opportunity THEN Amount, CloseDate
    ELSE Name, Email
  END
FROM Event

If they'll terminate your org for system abuse don't blame me ;)