Is it possible to combine rows within an SOQL query

Question

I am currently writing a query for the Campaign Members collection.

The collection contains many sets of CampaignMembers that are identical in every queried field except for CampaignId

When executing the SOQL query I'd like to be able to return one row per each of these sets of CampaignMembers. The resultant row would contain each of the fields that were identical across the set and one field contains an array of Campaign Ids.

Actual CampaignMembers Table

Id| ContactId | CampaignId

1 |    123      |      1
2 |    123      |      2
3 |    123      |      3
4 |    931      |      1

Rather than returning 4 separate objects with something like query('SELECT ContactId, CampaignId FROM CampaignMember')

Query Results

ContactId | CampaignId

   123    |   1
   123    |   2
   123    |   3
   931    |   1

I would ideally like to be able to return 2 objects

Query Results

ContactId | CampaignId

  123     | [1, 2, 3]
  931     | [1]

Is it possible to manipulate the data like this within an SOQL query, or is it necessary to fetch each record individually and combine them afterwards?

Answer 1

I'm afraid that SOQL alone isn't capable of doing what you want (heck, I don't know if plain 'old SQL could do that)...at least not exactly in the way you describe.

SOQL does give us the GROUP BY keyword, but a query looking like this

[SELECT COUNT(Id) FROM CampaignMember GROUP BY ContactId]

But that'll give you a result like this

ContactId| COUNT(Id)

123     | 3
931     | 1

The GROUP BY keyword makes the result of the query a List<AggregateResult> (as opposed to a List<CampaignMember>), and there isn't a way to figure out which 3 records are related to contactId 123 (at least, not without grouping by another field and iterating over the entire result set, which kinda defeats the purpose of your question).

The closest thing we can do to what you describe is to use a parent-child subquery (also called a Left Outer Join).

[SELECT Id, (SELECT CampaignId FROM CampaignMembers) FROM Contact]

The result of that would be

ContactId | Contact
123       | Contact(Id = 123, CampaignMembers = List<CampaignMember>{CampaignMember(CampaignId = 1), CampaignMember(CampaignId = 2), CampaignMember(CampaignId = 3)})
931       | Contact(Id = 931, CampaignMembers = List<CampaignMember>{CampaignMember(CampaignId = 1)})

Yes, the entire subquery (inner query) is contained within the SELECT clausef of the outer query.

Do make a note that when using a parent-child subquery, the "object" that you select from in the inner query is different. Instead of "CampaignMember", we use "CampaignMembers" (the plural).

CampainMembers is the child relationship name.

In many cases, the child relationship name is simply the plural name of the "child" object (Contacts, Opportunities, etc...) though there are exceptions (the child relationship name of the self-relationship on Account denoted by the ParentId field is "childAccounts"). If you made a custom relationship field, then you'll need to put __r on the end of the child relationship name