How to query multiple levels of lookup relationships?

[maxner-sdocs]

I've been able to find lots of examples of how to query for fields on a related object to the one being queried for (e.g. Account Name when querying on Opportunity) but not sure how to go multiple levels. For example:

// SELECT Name, Account.Name, Account.CreatedBy.Name FROM Opportunity

List<Opportunity> opps = (List<Opportunity>) SOQL.of(Opportunity.SObjectType)
    .with(Opportunity.Name)
    .with(Opportunity.Account.getDescribe().getRelationshipName(), Account.Name)
    .with(???)
    .toList();

Has anyone accomplished this before? Or do I need to run a separate query on Account to go that extra level?

[pgajek2]

Hi @maxner-sdocs ,

Great question!

I would suggest https://soql.beyondthecloud.dev/api/soql#with-related-field1---field5

List<Opportunity> opps = SOQL.of(Opportunity.SObjectType)
    .with(Opportunity.Name)
    .with(Opportunity.Account.getDescribe().getRelationshipName(), Account.Name)
    .with('Account.CreatedBy', User.Name)
    .toList();

Why in this way?
It's impossible to get multiple levels of relationship with getRelationshipName().

SOQL Lib use relationships name as a String, and fields as SObjectField so at least target fields are stored as reference.
Relationship Name: 'Account.CreatedBy',
Target Field: User.Name

SOQL Lib will do something like that:
Relationship Name + SObjectField.getDescribe().getName()
eg. 'Account.CreatedBy' + User.Name.getDescribe().getName() => Account.CreatedBy.Name

[maxner-sdocs]

Ah I see! So it's basically looking for everything leading up to the field itself. Thanks for the swift & thorough response @pgajek2 !!!

Also, I tested this out in my Dev Console and even took it a step further to avoid "magic strings" and this works as well (better for custom objects and especially those in Managed Packages which add a layer of complexity/nuisance from namespacing difficulties):

.with(Opportunity.Account.getDescribe().getRelationshipName() + '.' + Account.CreatedBy.getDescribe().getRelationshipName(),  User.Name)

[pgajek2]

Great idea @maxner-sdocs !

I will try to build some generic solution so you don't always need to add .getDescribe().getRelationshipName(). I will keep you posted about the progress! :)

[maxner-sdocs]

That'd be super cool :) if I have time in the near future I'll try to put up a PR myself and ask for your review on that. Thanks again!