3

Here's my query to get leads with attachments:

select id, name, (select id, name from attachments)
from Lead
where id in ( select parentId from attachment)

However I get an error saying that "the entity 'attachment' is not supported for semi join innter selects."

is my query right? Is there a way around it?

2 Answers 2

7

Your query would be awfully harsh on the database, because you're asking for every single attachment (full table scan), then trying to narrow it down. It's reasons like this that are probably why this type of semi-inner join isn't allowed. The optimized version of this query would be:

select id 
from lead 
where id in 
    (select parentid from attachment where parent.type = 'lead')

However, even if you write a nice, sane query with filters, it's still not allowed. You'll have to perform two queries: one to get all the ParentId values you're interested in, and another to get all the leads and the information you want returned.

4
  • Thanks! Can you tell me a little about the parent.type . I haven't seen the type part used before. Feb 23, 2015 at 19:29
  • Fields that can reference multiple data types (such as WhatId, WhoId, ParentId on Notes and Attachments, etc) can be filtered by the related object's type, and can return the related object's type. For example, SELECT Who.Type FROM Task WHERE Id in :Trigger.new and SELECT ParentId FROM Attachment WHERE Parent.Type = 'Account' are valid queries.
    – sfdcfox
    Feb 23, 2015 at 19:48
  • any other handy polymorphic goodness? Feb 23, 2015 at 19:56
  • Just TYPEOF, but it's still not GA, so it doesn't do much good to us developers in production.
    – sfdcfox
    Feb 23, 2015 at 20:17
1

I had the same problem and I ended up with a working example (on Note, but it's the same for Leads)

List<Note> noteList = [select parentId, title from Note where parent.type = 'Account'];
List<Id> ids = new List<Id>();
for (Note n: noteList) {
    ids.add(n.parentId);
}

List<Account> aList = [select name from Account where Id in :ids];
for (Account a: aList) {
    system.debug(a.name);
}

You must log in to answer this question.

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