1

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?

3
  • 2
    Rather than running additional queries, best practice would be to get everything you need the first time you run your queries, then put it all into maps so you can retrieve it later when you need to use it.
    – crmprogdev
    Jun 13, 2015 at 20:31
  • Another thing I noticed is that your second campaigns query probably won't retrieve anything since there aren't going to be any Venue__c records in a list of PatronTicket__EventInstance__c
    – martin
    Jun 13, 2015 at 20:41
  • @crmprogdev -- I'd love to figure out how to get it all in one query, but there's no actual relationship between the two objects. My understanding is that I cannot JOIN in SOQL the way I would in SQL between unrelated tables. Jun 13, 2015 at 20:48

2 Answers 2

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!

0
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];
3
  • Hello Martin, I don't quite know which relationship name you are referencing. There is no relationship between EventInstance__c and Campaign. They both have a lookup to a Venue, and the goal here is to find the campaigns related to venues from a group of EventInstances, but there's no actual relationship between EventInstance and Campaign. When I use your code as written, it errors with "Didn't understand relationship 'Campaign' in FROM part of query call. " Jun 13, 2015 at 20:21
  • It looks like I tried to answer the question too quickly. I would agree that your way of doing it is best. There isn't a way of querying up to the parent and down to different child objects in one statement, so you would have to break it up this way.
    – martin
    Jun 13, 2015 at 20:33
  • Days like these that I wish for a LISP style map or apply method! A custom iterator might be able to do it, but that seems just comically complex... Jun 13, 2015 at 20:35

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .