Ordering and restricting a subquery in Salesforce SOQL

Question

I am trying to restore the owner of the case based on partial match, where we select the most recent case that matches the partial match.

This is the query I'm trying to do:

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 does not seem happy as part of a subquery:

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

Similarly, if I omit 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') 

Order / constraint queries not allowed in SOQL subquery?

To clarify this problem, the scenario I'm dealing with looks like this ...

Salesforce can customize the "display format" for case numbers. If they choose the numbers "4", you get the numbers of such cases as:

• 0001
• 0125
• 1234
• 33456

You can change the numbers of your numbers to get the numbers of the following cases, as well as the case numbers above ...

• 000001
• 001234
• 033456

I did not want people to be confused by the LIKE operator, the problem is that 001234 and 1234 are different cases, so if the client supplies 1234 and I find two entries, I want to start with the assumption that they are the very last case.

So, consider a LIKE statement or an IN statement containing ('001234', '1234')

Answer 1

It is not possible to find documentation that indicates that LIMIT and / or ORDER BY do not work with subqueries, but I encountered the same error you were talking about.

However, it can work to start with a Case object and look at the user, similarly to the Lookup Relationships and Outer Joins section in SOQL documentation. I'm not sure if this will work for you, but this is something you can try.

Here is an example:

- Change -

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

It turns out that custom fields are not available because OwnerId is a polymorphic key that refers to a group or user. This means that the above will not work , sorry.

For work it is very difficult. You will need to create a custom search field called "User User" or something else. This will save the search link for the user if the Owner is a user (this can be verified by comparing the beginning of OwnerId with "005", the prefix of the user ID). This field should be filled with after insert after update trigger. All values ​​for this new field must be loaded for previously existing cases. But, as soon as you have this new User User field, you can access the user's user fields through SOQL using it.

Answer 2

ORDER BY and LIMIT do not make sense in your subquery because you are not returning records from the subquery. Instead, the subquery only builds a list of identifiers used to filter the main request.

If you use a subquery so that the subquery entries are returned, these provisions are accurate. For example, this works:

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

Answer 3

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

In particular, the TYPEOF function provides access to field types through polymorphic search queries: 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 the developer preview.

 SELECT TYPEOF Owner WHEN User THEN CustomField__c END FROM CASE 

Answer 4

Have you tried 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