function readOnly(count){ }
Sign Up ›
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
Learn More

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
Bob HatcherBob Hatcher 

SOQL: Return Most Recent Attachment w/ Body

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.
Then I tried: 
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.
Navee RahulNavee Rahul
Hello Bob,

may be this ??.

SELECT  Id , Date__c, (SELECT   Id, Body ,Name,Lastmodifieddate,createddate  FROM Attachment Order By Lastmodifieddate DESC Limit 1) FROM    Software_License__c WHERE   Account__c = :CurrentUser.AccountId ORDER BY Date__c DESC


Thanks
D Naveen Rahul.
Bob HatcherBob Hatcher
Thank you Naveen, however that doesn't work since you can't return a binary (blob/attachment body) in a subquery.

I don't think this is going to work in a single query. A colleague of mine came up with this: 
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])
                {
                    licenseKeyDoc = att;
                }
            }

which, although a nested query, the LIMIT 1 clause in the outer loop guarantees that the inner loop only fires once.