3

I have a data model where I have an Object, Software_License__c, which contains a Lookup to an Account. There are Attachments against the Software License. I can safely assume the Software License has one attachment, but a given Account may have multiple Software Licenses. The Software License contains a DateTime called Date__c.

I need to return the Attachment (with Body) of the most recent Software License given the ID of the Account lookup.

I've tried a few things.

SELECT  Id , Date__c, (SELECT   Id, Body ,Name FROM Attachments LIMIT 1)
FROM    Software_License__c
WHERE   Account__c = :CurrentUser.AccountId
ORDER BY Date__c DESC

This does not work because you can't return binary data (e.g., the attachment body) from a subquery.

select  Id, Body, Name  from    Attachment 
where   ParentId in (select Id  from Software_License__c where Account__c = :CurrentUser.AccountId ORDER BY Date__c DESC LIMIT 1 )

that doesn't work because you need to select Date__c to ORDER BY it.

I can't think of a way to make this work with just one query. Anyone have a thought? TIA.

  • 1
    I've always done this with 2 queries – cropredy Aug 2 '16 at 23:13
  • Why order by for an inner join? – Adrian Larson Aug 2 '16 at 23:38
  • In the end we did make two loops. While it's a little jarring to see a nested query, in the outer loop we had a LIMIT 1, which means it's no more than two queries. Less desirable than just one, but gets the job done. Outer Loop: for(Software_License__c sl : [select Id from Software_License__c where Account__c = :CurrentUser.AccountId order by Date__c desc limit 1]) Inner Loop: for(Attachment att : [select Id, Body, Name from Attachment where ParentId = :sl.Id limit 1]) – Bob Hatcher Aug 4 '16 at 13:48
2

You should be able to use the Id as a ORDER BY parameter rather than the date. Try this (untested, btw):

SELECT  Id, Body, Name  
FROM    Attachment 
WHERE   ParentId IN (SELECT Id  
                     FROM Software_License__c 
                     WHERE Account__c = :CurrentUser.AccountId 
                     ORDER BY Id DESC LIMIT 1 ) 

This is assuming that date roughly corresponds to created date, of course.

  • Thank you, unfortunately I can't make that assumption. But I like your idea that Id's are sequential so you could, theoretically, order on them. – Bob Hatcher Aug 4 '16 at 13:47
0

(Restating as answer my above comment)

In the end we did make two loops. While it's a little jarring to see a nested query, in the outer loop we had a LIMIT 1, which means it's no more than two queries. Less desirable than just one, but gets the job done.

for(Software_License__c sl : [select Id from Software_License__c where Account__c = :CurrentUser.AccountId order by Date__c desc limit 1]) 
{
    for(Attachment att : [select Id, Body, Name from Attachment where ParentId = :sl.Id limit 1]) 
    {
    }
}

Kudos to my developer colleagues for the assist on this one.

  • That's still a query in a for loop, which seems highly inadvisable. – Adrian Larson Aug 19 '16 at 17:19
  • 1
    The outer loop is LIMIT 1 which guarantees it only runs once. I agree the aesthetics of it are terrible but in the end it's guaranteed to be just two queries. – Bob Hatcher Aug 22 '16 at 14:29

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.