SOQL: Is there a way to select Accounts with sub selecting all their Contacts which are created by the same user as their related Account?

Question

The title say it all: Is there a way to select Accounts with sub selecting all their Contacts which are created by the same user as their related Account?

I'm trying to mix conditions from inner and outer query like

[ select Id, Name, (select Id, Lastname from Contacts c  ) 
from Account a where a.CreatedById = c.CreatedById ]

or

[ select Id, Name, 
    (select Id, Lastname from Contacts c where c.CreatedById = a.CreatedById ) 
from Account a ]

Both are not working. Is there anything coming close to such a filtering?

Answer 1

You cannot do this in SOQL, unfortunately.

One workaround is to create a checkbox or other formula field on the Contact object that determines whether it is owned by the same user as the Account, then use that formula field in your query.

The checkbox formula field might be:

(OwnerId == Account.OwnerId)

Then your query looks like

select Id, Name,
    (select Id, Name
    from Contacts
    where Same_Owner_Formula__c = TRUE)
from Account