I want to query Lead and Contact object using SOQL and retrieve Id of either of object.
For example:
select Id from Lead where email = 'a@a.com'
select Id from Contact where email = 'a@a.com'
These are 2 different queries as applied on different SObjects. Is there any way to run both query in single request (i.e. by some kind of query separator)?
I'm using Partner WSDL for this.
What would the best approach?
Best Answer
This is a perfect example of where it makes sense to use SOSL, rather than SOQL. The following SOSL search query, made using the Partner WSDL from Java (adapted from this example, which also includes source code examples for C#), should do the trick:
Why is SOSL appropriate here, rather than SOQL?
If you weren't doing an exact text search against Contact and Lead, I would also add that SOSL would be essential for doing this sort of search in an organization with any significant data volume, since Email fields are ordinarily not indexed, and so a SOQL query against a non-custom-indexed Email field on any Custom Object would require a full table scan, rendering your query completely useless in an org with lots and lots of records. But because you are doing an exact text match search against against the Email field on Contact and Lead, you would technically be fine with SOQL as far as performance goes, because the Email field is indexed on
Contact
andLead
. Why do I mention this here if it doesn't apply? Because it's good to get in the habit of using the right tools for the right scenario, and to know why they're the right tools. SOSL is perfect for this type of search query.There is one gotcha to watch out for, though --- SOSL's indexes do not update immediately. In my experience, it takes at least 30 seconds for the SOSL indexes to update after records are modified. So if, for example, a user just created a new Contact, and then ran this SOSL query 10 seconds later, that Contact record would not be returned in the search. If this is unacceptable for your situation (which I doubt it is), you'd have to use SOQL for this scenario.