Multiple SOQL queries in single request

Question

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 = '[email protected]' select Id from Contact where email = '[email protected]'

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?

Answer 1

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:

    String email = "[email protected]";

    // (OPTIONAL, depending on how sanitized your inputs are already)
    // Escape single quotes that might occur in the string to prevent SOSL Injection
    // http://commons.apache.org/proper/commons-lang/apidocs/org/apache/commons/lang3/StringEscapeUtils.html#escapeEcmaScript(java.lang.String)
    email = StringEscapeUtils.escapeEcmaScript(email);

    String soslQuery = 
        "FIND {" + email + "} IN Email FIELDS " +
        "RETURNING " +
        "Lead(Id,Email), Contact(Id,Email)";
    // Perform SOSL query
    SearchResult sResult = partnerConnection.search(soslQuery);
    // Get the records returned by the search result
    SearchRecord[] records = sResult.getSearchRecords();
    // Create lists of objects to hold search result records
    List<SObject> contacts = new ArrayList<SObject>();
    List<SObject> leads = new ArrayList<SObject>();

    // Iterate through the search result records
    // and store the records in their corresponding lists
    // based on record type.
    if (records != null && records.length > 0) {
      for (int i = 0; i < records.length; i++){
        SObject record = records[i].getRecord();
        if (record.getType().toLowerCase().equals("contact")) {
          contacts.add(record);
        } else if (record.getType().toLowerCase().equals("lead")){
          leads.add(record);
        }
      }

Why is SOSL appropriate here, rather than SOQL?

• A SOSL search here can be made using the Partner WSDL with only a single API call, whereas a SOQL query to achieve the same result would require 2 API calls, burning through an organization's daily limits twice as fast.
• SOSL can return records of multiple types of SObjects all at once --- SOQL cannot.

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 and Lead. 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.

Answer 2

I don't think you can use SOQL to achieve this, I think you will need to use SOSL. See reference & example code here: link

Answer 3

With SOQL you need 2 queries for that. As you're using an API, you could also consider developing a custom Apex Webservice or REST service that returns the combined data in a custom data structure. While this would still make 2 SOQL queries, you could reduce your API requests.

Answer 4

No, there is nothing like a "UNION" operator, or a";" query separator. In MS-SQL either of those tools could help get the result you want.

However, in this case, you should consider (or explain) why you need one query instead of 2. In particular, if you need to get a mixed set of records to combine in a mixed list, then 2 queries that contribute to a List<sObject> would work fine, with code like this:

list<sobject> leadsAndContacts  = new list<sobject>();
leadsAndContacts.addall(select id, name from lead)
leadsAndContacts.addAll(select id, name from Contact)