SOSL, friend or foe?

by

As requested by Chris Peterson I’d like to dig a bit more into SOSL. This is as much a lesson for me as anyone else, and I’d be ecstatic if anyone out there could offer a bit more depth on the topic. SOSL (or sossel) is Salesforce’s search language, and in my experiences definitely has it’s advantages(which are significant in the CRM) and limitations(which may stem from me/us just not knowing how to use it properly). First let’s make sure you have the standard docs memorised.

Basic doc on SOSL within Apex code

API doc, thorough on syntax although might not have some common examples.

Doc for dynamic SOSL

Right, now that we have those committed to memory we can delve into what I think are the advantages and disadvantages of SOSL..

Advantages

  • It’s concise & simple to understand when read (not necessarily easy to construct) eg.
    List<List<SObject>> searchList = [FIND ‘map*’ IN ALL FIELDS RETURNING Account (id, name), Contact, Opportunity, Lead]
  • Without reading any documentation I can infer that this is going to find all sentences beginning with the word(slightly misleading as you’ll read later) ‘map’ and will retrieve a list of 4 different types of objects.
  • It’s a quick way to search all fields of an object, including textareas by specifying ‘IN ALL FIELDS’. Alternatively if I want to search a list of textareas that I’ve fetched using SOQL there’d be a significant increase in my code verbosity.

Disadvantages

  • Queries are simple, but lack configuration I’d like to see eg. you can’t specify the exact fields that you want to search or case-insensitive searching.
  • Cannot search child objects and return their parents directly e.g. I’m building an Ideas site and I’d like to search all Idea comments and return the parent Idea of these each comment when a searchphrase is found.
  • Results returned in relatively complex structure i.e. even if you search through a single object type the results will be returned as a List of Lists of SObjects.
  • Cannot search for a single character or less(This is a small’un but it still makes me want to slap someone silly).

A few common SOSL tricks I use

  • In the API document you’ll find a sentence that says ‘Do not use the asterisk at the beginning of a search term.‘ I have tried prepending phrases with an asterisk, and while this does not throw an error it yields no gain either. From what I can infer it seems that each search phrases has an invisible asterisk prepended by the engine that performs searches e.g. searching for ‘map*’ will yield results if it finds ‘map’, ‘mapping’ or ‘bumpmap’. This can make things tricky, for example let’s assume that you only want to search for phrases that start with the word ‘map’, how do you stop that invisible first asterisk from being prepended? I’d have to say, “Dunno.”, but I’d guess you’d either have to use a complex combination of SOSL logical operators and exact-phrase searches, or parse the results after the SOSL search and narrow the results down programmatically.
  • When I use an input area as a ‘search input’ I often use JavaScript to catch instances where a user enters <2 characters and displays an alert of some sort.
  • If you need to use conditional logic in your search phrases e.g. AND or OR, the syntax is a little queer. I could not find a dynamic SOSL example of this, but found something close to what I needed in the API doc,

    /** Dynamic SOSL **/
    String searchString = ‘FIND \’le* OR il\’IN ALL FIELDS RETURNING Account (id, name), Contact’;
    List<List<SObject>> dynamicSearchList = search.query(searchString);

    /** Alternative Apex Syntax **/
    List<List<SObject>> searchList = [FIND ‘le* OR il’ IN ALL FIELDS RETURNING Account (id, name), Contact];

    Now this is a teeny-tiny lil’ thang, but I would’ve expected the “FIND ‘le* OR il'” bit to be “FIND ‘le*’ OR ‘il'”. I thought I’d give you guys the heads up just in case you find yourselves similarly situated;)

Extensions I’d like to see

  • SOSL for Apex Types. Quite often I work with wrapper classes, or lists of custom types. I would be very happy if I could quickly search through these guys. That said I’ve built a generic search engine of sorts although it requires a number of describes and can very quickly hit governor limits(and is probably not as fast as something built closer to the nuts and bolts of the platform) so I’d still give two thumbs up if this was developed.
  • More info about your results. Sometimes, not always, but often enough, I’d like to know how many instances of my search phrase were found, and the field they were found in e.g. 3 instances of the word ‘map’ were found in Custom_Field__c of object Custom_Object__c.
All in all, I like SOSL. He’s a good-natured bloke and does his job well. I think he’s a bit young and after a few more of life’s little lessons he may grow up into a powerful.. okay I’ve taken this analogy too far, I’m sure you see where I’m going with this. I think that as a developer you need to fight a few battles alongside SOSL and SOQL before you get a feel for which to use and when.

I invite any SOSL queries(pun intended) you might have, and I’ll do my best to give you a hand.

4 thoughts on “SOSL, friend or foe?”

  3. Very useful. Thanks for putting this.

    I’m building generic search engine on Case object. But its quite weird SOSL is returning result on subject field.

    Any clue why so?

    Reply

Leave a Comment