7

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?

  • 2
    SOSL wouldn't ever work: I regularly name my accounts with non-clashing symbols, like ♥LOVE♥. I'm sure plenty of people forget that there's many languages out there, many of which don't use a-z to begin with. You couldn't possibly construct a meaningful search phrase out of that. – sfdcfox Jan 22 '14 at 20:14
  • 1
    Could you "cache" the Names from objects you're interested in in a helper object and query that one? I'm thinking about Analytic Snapshot / simple trigger that means something can act like a view on multiple source objects. – eyescream Jan 22 '14 at 21:25
  • 1
    I more stupid idea. Can you tag your records with tag name = record's name :D – eyescream Jan 22 '14 at 21:47
  • 1
    the search where id in approach is very inefficient, you'd be better just running the multiple soql queries. – superfell Aug 11 '15 at 2:54
2

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
  • Nice idea! But I think the field will have to be marked as ext. id to be used in searches? – eyescream Jan 23 '14 at 12:22
  • 1
    I gave this a go and it was searchable just by virtue of being text @eyescream - shame that ID is not!!! – bigassforce Jan 25 '14 at 4:36
7

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.

  • 2
    This place would be more helpful if people downvoting answers commented. – bigassforce Aug 11 '15 at 0:27
  • if you have large amounts of data you'll get burned by search result truncations the filter in the returning clauses are applied after the search index is processed, so if you hit the search result limits, you can not find your records. – superfell Aug 11 '15 at 2:53
  • Super insightful @superfell - tried this in an org with 229272 records and the result set is... (you guessed it) an empty list; thanks for the heads up. – bigassforce Aug 11 '15 at 10:08
4

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.

  • 1
    Side note... I just realized I linked to my own idea, and it has never been voted on. Do people not need this feature? – sfdcfox Jan 22 '14 at 20:12
  • 2
    Interesting to put a name to the sfdcfox via Salesforce ideas. Pleased to meet you Brian :) – Daniel Ballinger Jan 22 '14 at 20:17
  • 1
    He's going to delete his comment and flag yours for moderator attention in 3...2...1 ;) – eyescream Jan 22 '14 at 20:18
  • 1
    My real name, and age, are on my profile. I used this moniker to associate this account with my DeveloperForce profile. – sfdcfox Jan 22 '14 at 20:27
  • 1
    I think I need to add an About Me. Thanks for bringing that to my attention! – sfdcfox Jan 22 '14 at 20:51
1

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 ;)

  • 1
    And, since we're going for workarounds, why not just call the REST API and call retrieve on up to 2,000 rows at once? – sfdcfox Jan 22 '14 at 20:15
  • ah yes, good call @eyescream - Activities being the original polymorphic metadata – bigassforce Jan 22 '14 at 21:40

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.