8

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?

4
  • 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, 2014 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, 2014 at 21:25
  • 1
    I more stupid idea. Can you tag your records with tag name = record's name :D
    – eyescream
    Jan 22, 2014 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, 2015 at 2:54

4 Answers 4

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
2
  • Nice idea! But I think the field will have to be marked as ext. id to be used in searches?
    – eyescream
    Jan 23, 2014 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!!! Jan 25, 2014 at 4:36
8

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.

3
  • 2
    This place would be more helpful if people downvoting answers commented. Aug 11, 2015 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, 2015 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. Aug 11, 2015 at 10:08
5

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.

5
  • 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, 2014 at 20:12
  • 2
    Interesting to put a name to the sfdcfox via Salesforce ideas. Pleased to meet you Brian :) Jan 22, 2014 at 20:17
  • 1
    He's going to delete his comment and flag yours for moderator attention in 3...2...1 ;)
    – eyescream
    Jan 22, 2014 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, 2014 at 20:27
  • 1
    I think I need to add an About Me. Thanks for bringing that to my attention!
    – sfdcfox
    Jan 22, 2014 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 ;)

2
  • 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, 2014 at 20:15
  • ah yes, good call @eyescream - Activities being the original polymorphic metadata Jan 22, 2014 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.