Ordering and Limiting A Subquery In Salesforce SOQL

Question

I am attempting to retrieve the owner of a case, based on a partial match, where we choose the most recent case that matches the partial match.

This is the query I am attempting:

SELECT User.CustomField__c 
FROM User 
WHERE User.Id IN (
    SELECT OwnerId 
    FROM Case 
    WHERE Case.CaseNumber LIKE '%1026' 
    ORDER BY Case.CreatedDate DESC LIMIT 1)

The following query works on its own, but doesn't seem happy as part of the subquery:

SELECT OwnerId 
FROM Case 
WHERE Case.CaseNumber LIKE '%1026' 
ORDER BY Case.CreatedDate DESC LIMIT 1

Equally, if I drop the ORDER BY and LIMIT it works:

SELECT User.NVMContactWorld__NVM_Agent_Id__c 
FROM User 
WHERE User.Id IN (
    SELECT OwnerId FROM Case 
    WHERE Case.CaseNumber LIKE '%1026')

Are order / limit queries not allowed in a SOQL subquery?

Just to clarify this issue, the scenario I am dealing with looks like this...

A Salesforce organisation can configure the "display format" for Case Numbers. If they select "4" digits, you get case numbers like:

• 0001
• 0125
• 1234
• 33456

It is possible to reconfigure your case numbers to get the following case numbers as well as the case numbers above...

• 000001
• 001234
• 033456

I didn't want people to be confused by the LIKE statement, the issue is that 001234 and 1234 are different cases, so if a customer supplies 1234 and I find two records, I want to start off assuming that they are the most recent case.

So either consider the LIKE statement or an IN statement that contains ('001234', '1234')

Legault

Answer 1

There is no documentation I could find that specifies that LIMIT and/or ORDER BY do not work with subqueries, but I ran into the same error you mentioned.

However, it may work to start at the Case object and look up to the User, similar to the Lookup Relationships and Outer Joins section in the SOQL documentation. I'm not sure if this would work for you, but it's something you may want to try.

Here's an example:

-- Edit --

SELECT OwnerId, Owner.CustomField__c FROM Case WHERE CaseNumber LIKE '%1026' ORDER BY CreatedDate DESC LIMIT 1

Turns out Custom Fields are not accessible because OwnerId is a polymorphic key referencing either Group or User. That means the above won't work, sorry.

To work-around this is very complicated. You would have to create a custom lookup field called "User Owner", or something. That would store a lookup reference to the User, if the Owner is a User (this can be checked by comparing the beginning of OwnerId to '005', the User ID prefix). That field would need to be populated using a after insert, after update Trigger. All values for this new field would need to be dataloaded for previously existing Cases. But, once you have this new "User Owner" field, you can access custom fields on User through SOQL, using it.

Wabash

Hi Matthew, I don't think you can select custom fields in a relationship query, you can only access common fields that would be the same across the different types of owner. Sadly, this is the reason I had to move to a sub-query. – Legault

Ah, you're right. OwnerId is a polymorphic key; it can reference either a Group or a User. That makes custom fields on the user inaccessible. It gives me another idea, though. – Wabash

Answer 2

ORDER BY and LIMIT don't make sense in your subquery because you're not returning records from the subquery. Instead, the subquery just builds a list of IDs used to filter the main query.

If you use a subquery in a way that the subquery records are returned, these clauses are fine. For example, this works:

SELECT Name, 
    (SELECT FirstName, LastName FROM Contacts ORDER BY LastName LIMIT 10) 
FROM Account

Gaige

The idea of what I'm trying to do does make sense - if there are multiple cases with the same case number, I want to use only the most recent one. - Don't get me started on why it is possible to duplicate case numbers. – Legault

I see. I don't know of a way to accomplish that in a single SOQL query. – Gaige

Answer 3

I think it's worth adding that with new features of SOQL that were not available when this question was first asked and answered, that the approach of querying from Case should now be viable (with access to custom fields).

In particular the TYPEOF feature provides access to type specific fields through polymorphic lookups: http://www.salesforce.com/us/developer/docs/soql_sosl/Content/sforce_api_calls_soql_select_typeof.htm

It is worth noting that this feature is still in Developer Preview.

SELECT 
  TYPEOF Owner
    WHEN User THEN CustomField__c
  END
FROM CASE

Juta

Answer 4

Did you try switching your query around to be something like this?

SELECT OwnerId, (select id from user)
    FROM Case 
    WHERE Case.CaseNumber LIKE '%1026' 
    ORDER BY Case.CreatedDate DESC LIMIT 1

Paresis

Although this query works, it doesn't give you access to custom fields. – Legault