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.

3
  • 1
    I've always done this with 2 queries
    – cropredy
    Aug 2, 2016 at 23:13
  • Why order by for an inner join?
    – Adrian Larson
    Aug 2, 2016 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]) Aug 4, 2016 at 13:48

2 Answers 2

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.

1
  • 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. Aug 4, 2016 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.

2
  • That's still a query in a for loop, which seems highly inadvisable.
    – Adrian Larson
    Aug 19, 2016 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. Aug 22, 2016 at 14:29

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .