Get a list of one column from a SOQL result?

Question

I have a SOQL query result that I use several times in a method. It pulls in many columns that I use for a few things. This includes a relationship column, that I do a query on another object. Is there a more elegant way to get at the relationship ID column than looping through my result. For example:

List<PatronTicket__EventInstance__c> instances =[SELECT
                              Id,Name,PatronTicket__InstanceDate__c,
                              PatronTicket__Venue__c 
                              FROM PatronTicket__EventInstance__c 
                              WHERE PatronTicket__TicketableEvent__c = :id];

List<Id> cids = new List<Id>();
for(PatronTicket__EventInstance__c e: instances) {
     cids.add(e.PatronTicket__Venue__c);
}

List<Campaign> campaigns = [SELECT Id,Name 
                            FROM Campaign
                            WHERE Venue__c IN :cids];

I then go on to use both instances and campaigns in different contexts. I'm aware I can do another query in the WHERE clause, like:

List<PatronTicket__EventInstance__c> instances =[SELECT
                              Id,Name,PatronTicket__InstanceDate__c,
                              PatronTicket__Venue__c 
                              FROM PatronTicket__EventInstance__c 
                              WHERE PatronTicket__TicketableEvent__c = :id];

List<Campaign> campaigns = [SELECT Id,Name 
                            FROM Campaign 
                            WHERE Venue__c IN (
                              SELECT PatronTicket__Venue__c 
                              FROM PatronTicket__EventInstance__c 
                              WHERE PatronTicket__TicketableEvent__c = :id
                            )];

But I can't decide if that's more understandable or a more efficient way of doing this query. In this version I have to maintain the criteria twice. What say the hivemind? What's the best way to accomplish this?

Answer 1

I found my answer while trying very hard about how to fit it all into one query, thanks to @crmprogdev's prodding.

List<PatronTicket__Venue__c> venues = [Select Id,Name,
    (SELECT Id,Name,PatronTicket__InstanceDate__c
     FROM PatronTicket__Event_Instances__r 
     WHERE PatronTicket__TicketableEvent__c = :id
    ),
    (SELECT Id,Name 
     FROM Campaigns__r
    )
    FROM PatronTicket__Venue__c 
    WHERE Id IN 
    (SELECT PatronTicket__Venue__c 
     FROM PatronTicket__EventInstance__c 
     WHERE PatronTicket__TicketableEvent__c = :id
    )
];

List<PatronTicket__EventInstance__c> instances = new List<PatronTicket__EventInstance__c>();
List<Campaign> campaigns = new List<Campaign>();

for(PatronTicket__Venue__c v:venues) {
    instances.addAll((List<PatronTicket__EventInstance__c>)v.getSobjects('PatronTicket__Event_Instances__r'));
    campaigns.addAll((List<Campaign>) v.getSobjects('Campaigns__r'));
}

This method uses Venue__c as a sort of junction, and the getSobjects method that is inherited from SObject to get the subquery members.

One of the tricks was remembering to look for the Child Relationship Name in the relationship definition (and remembering that it doesn't always follow a reasonable pattern...)

Still, the criteria for WHICH event instances are being queried needs to be repeated in the first subquery and in the overall, otherwise we get too many records. But, it's all one query!

Answer 2

List<PatronTicket__EventInstance__c> instances =[SELECT
                          Id,Name,PatronTicket__InstanceDate__c,
                          (SELECT Id,Name 
                        FROM Campaign // Replace with relationship name.
                        WHERE Venue__c IN (
                          SELECT Id,PatronTicket__Venue__c 
                          FROM PatronTicket__EventInstance__c 
                          WHERE PatronTicket__TicketableEvent__c = :id
                        )) 
                          FROM PatronTicket__EventInstance__c 
                          WHERE PatronTicket__TicketableEvent__c = :id];