SOQL Across Multiple Objects vs SOSL
Welcome back my children. Once again, I’ve encountered a situation where I’ve needed to improvise. For these code examples, remember to use the “expand code (<- ->)” option on the snippets to see everything.
The Issue(s):
1. I needed a way to be able to take a given object id and determine it’s matching type
2. I needed to be able to search across multiple objects for matches.
The non- working, but intuitive approach:
I figured I would just do a SOSL search across the types that I know the ID can be matched again. Then using a basic ternary statement or so, map the type:
1 2 3 4 5 6 7 8 9 |
//get the id from the page param string pID = ApexPages.CurrentPage().getParameters().get('id'); //The SOSL attempt to search IDs List<List<sObject>> objResults = [FIND :pID IN ALL FIELDS RETURNING Book__c(Id,Name), Author__c(Id,Name), Source__c(Id,Name) LIMIT 1]; //determine the type accordingly based upon which one was populated DataContext.ItemType = objResults[0].size()>0? 'Book__c' : (objResults[1].size()>0? 'Author__c' : (objResults[2].size()>0? 'Source__c' : '') ); |
The problem with this approach is that the Id field isn’t indexed in SOSL. So that leaves me with this option:
The working solution (for under APIv26):
I solved the problem by pushing the results of the SOQL queries into the same kind of list my SOSL would’ve returned any way. Keep in mind that at least it has a run time of O(C), where C=the number of custom objects to compare against, in my case, C=3:
1 2 3 4 5 6 7 8 9 10 |
//get the id from the page param string pID = ApexPages.CurrentPage().getParameters().get('id'); //The hack for a search across multiple objects with SOQL //and with one line of code ;) List<List<sObject>> objResults = new List<List<sObject>>{[SELECT Id,Name FROM Book__c WHERE Id=:pID],[SELECT Id,Name FROM Author__c WHERE Id=:pID],[SELECT Id,Name FROM Source__c WHERE Id=:pID]}; //match the types based on the order in which the SOQLs are executed DataContext.ItemType = objResults[0].size()>0? 'Book__c' : (objResults[1].size()>0? 'Author__c' : (objResults[2].size()>0? 'Source__c' : '') ); |
The Working Solution for APIv26 and up
We can skip the whole ternary statement thing (I still needed the search on multiple objects):
1 2 |
Id pRealId = ApexPages.CurrentPage().getParameters().get('id'); DataContext.ItemType = pRealId.getSObjectType().getDescribe().getName(); |
There is also the alternative to hard coding the object types to instead say objResults.size()>0? objResults[0].getSObjectType().getName() : ''; . The only reason I didn’t use that is because I made reference to run times, and I can no longer account for an actual run time after I start making calls to black box methods such as getSObjectType() and getName(). Quite frankly, the impact should be minimal based on my experience. Me personally, I use the last solution shown (y).
There you have it! As usual – stay thirsty. 😉