Querying Custom Objects via Junction Objects

Question

I have a junction Object called Service_Transaction_Type__c that creates a many-to-many relationship between two other custom objects (Service_Offering__c and Transaction_Type__c). I am also working with another custom object that has a rather long name (referred to as CustomObject__c here). CustomObject__c is a junction Object between Service_Offering__c and Account.

Typically, the only users who would be logged in as ":UserInfo" are customer portal users, so they will always have an associated contact.

Below is how I have started this query:

       User u = [SELECT Id, ContactId, FirstName FROM User WHERE Id = :UserInfo.getUserId()];

       Contact c = [SELECT Id, LastName, AccountId FROM Contact WHERE Id =:u.ContactId];

       Account a = [SELECT Id, Name FROM Account WHERE Id = :c.AccountId];

       List<CustomObject__c> sn = [SELECT Id, Name, Service_Offering__r.Name FROM CustomObject__c WHERE Account__c = :a.Id]; 

What I need to do next is come up with a list of all of the Transaction_Type__c names for all of the Service_Offering__c's that were returned when I queried the List(CustomObject__c> sn.

I have tried a number of ways of going about doing this. Some involving for loops or trying to create a list of Service_Offering__c's to query next. Below is an example of how I have attempted this:

List<Service_Offering__c> serviceList = new List<Service_Offering__c>();

    for (CustomObject__c snanaRecord : sn){
        serviceList.add(snanaRecord.Service_Offering__r);    

   }

List<Transaction_Type__c> trxNameList = [SELECT Name FROM Transaction_Type__c WHERE Service_Transaction_Type__r.Service_Offering__c IN :serviceList];

The error that keeps getting thrown is: "Didn't understand relationship 'Service_Transaction_Type__r' in field path. If you are attempting to use a custom relationship, be sure to append the '__r' after the custom relationship name." I am using the "__r" in the field path. I would appreciate any input as to where I may be going wrong here.

Thanks,
CP

Answer 1

'Transaction_Type__c WHERE Service_Transaction_Type__r.Service_Offering__c'

Here Service_Transaction_Type is the child of Transaction type . you can't query it using this way(means you can't directly access from here).

use inner query to get all Service_Transaction_Type record and from there apply the Service list Filter in where condition.

or another approach is query the Service_Transaction_Type object and get parent record of transaction and then use Service Type in where condition.

So you can go with inner query or the second approach. If you want to use inner query then to get the relationship name go to child object select lookup field and there you will get the relationship name add '__r' in the name end.