Apex Code Development You need to sign in to do that
Don't have an account?
Babugarh Query parent object by filter on the child object
I have a query below, I would like to filter the Contact object based on the filters on the campaign Members. I understand that I can do that through for loops. But I am trying to see if there are other ways and update myself.
Select
c.Id,
c.Account.xyz__c,
(Select
Id,
Status
From
CampaignMembers
WHERE
status = 'sent')
From
Contact c
Please do note that the above query will go as a dynamic SOQL and the dynamic SOQL will build the account fields in the query based on parameters set in the custom setting.
Yes, this is one alternative to the original stated goal of filtering contacts by campaignmember field values.
The downside to this approach is that you may and up with multiple refs to the same contact, i.e. there may be more than one campaignmember with status of "Sent" for the same contact which will result in taking the cost of additional and perhaps unneeded rows in the result set.
Fortunately there is another approach which addresses that potential issue which is to leverage the Semi-joins support. The same query would look like this:
See the API Documentation for more info about these types of queries.
All Answers
Your query will not filter the contacts based upon the campaign member status... if you want to filter contacts based upon campaignmember status then you have to write the below query
Select ContactId, Contact.FirstName from CampaignMember where status = 'Sent' and contactId != null
Yes, this is one alternative to the original stated goal of filtering contacts by campaignmember field values.
The downside to this approach is that you may and up with multiple refs to the same contact, i.e. there may be more than one campaignmember with status of "Sent" for the same contact which will result in taking the cost of additional and perhaps unneeded rows in the result set.
Fortunately there is another approach which addresses that potential issue which is to leverage the Semi-joins support. The same query would look like this:
See the API Documentation for more info about these types of queries.
I accept that.
I was just checking if I could perform it with only one query. Looks like that it is not possible. Thanks for your efforts.
Sure thing but I want to understand why you think you can't do this in one query. Isn't the example above basically what you want?
Don't be confused by the double "select" statements, this is one query (in apex) and requires only one request via the API as well.