Apex Code Development You need to sign in to do that
Don't have an account?
Robbert Bos2 SOQL where statement in list
I have a public list where in I want to check if the record owner is the same as a related object, but I keep getting an error. Anybody know if this is possible?
public List< Custom__c > getBalance2(){
return [SELECT id, name, State__c, OwnerId, Related__r.OwnerId
FROM Custom__c
WHERE State__c = 'Active'
AND Related__r.OwnerId = :Balance2.OwnerId ];
}
The wrong workaround is the following requests because it is any owner in the two objects (bad):
- select id, ownerid , firstname,lastname from contact where ownerid in (select ownerid from account)
- select id, ownerid, firstname,lastname from contact where ownerid not in (select ownerid from account)
1. the same owner can be not on the parent account (it lacks the join)2. seems better but too restrictive (it also lacks the join)
The common technique when you want to compare any fields is to use formula fields (checkbox) building cross-object formulas.
The field formula here in custom object is : isEqualsParentOwner__c = Related__r.OwnerId = OwnerId
SELECT id, name, State__c, OwnerId, Related__r.OwnerId
FROM Custom__c
WHERE State__c = 'Active'
AND isEqualsParentOwner__c = true
The only problem is the performance with these formulas.
Bad: Unknown error parsing query
select id, account.ownerid, ownerid
from contact
where
account.ownerid = owneridCorrect:
select id, account.ownerid, ownerid
from contact
where isEqualsParentOwner__c = true
with isEqualsParentOwner__c : a new formula field (checkbox) for Contact = account.ownerid = ownerid
And finally, you can use:only one request:
select id, account.ownerid, ownerid, firstname,lastname, isEqualsParentOwner__c
from contact
Regards
All Answers
The wrong workaround is the following requests because it is any owner in the two objects (bad):
- select id, ownerid , firstname,lastname from contact where ownerid in (select ownerid from account)
- select id, ownerid, firstname,lastname from contact where ownerid not in (select ownerid from account)
1. the same owner can be not on the parent account (it lacks the join)2. seems better but too restrictive (it also lacks the join)
The common technique when you want to compare any fields is to use formula fields (checkbox) building cross-object formulas.
The field formula here in custom object is : isEqualsParentOwner__c = Related__r.OwnerId = OwnerId
SELECT id, name, State__c, OwnerId, Related__r.OwnerId
FROM Custom__c
WHERE State__c = 'Active'
AND isEqualsParentOwner__c = true
The only problem is the performance with these formulas.
Bad: Unknown error parsing query
select id, account.ownerid, ownerid
from contact
where
account.ownerid = owneridCorrect:
select id, account.ownerid, ownerid
from contact
where isEqualsParentOwner__c = true
with isEqualsParentOwner__c : a new formula field (checkbox) for Contact = account.ownerid = ownerid
And finally, you can use:only one request:
select id, account.ownerid, ownerid, firstname,lastname, isEqualsParentOwner__c
from contact
Regards