With Summer '23 release we can query upto 5 levels down in Parent-Child Relationships. See this Salesforce release notes
My sandbox is on Summer '23 release and I was able to test my custom objects successfully
SELECT Name,
(SELECT Name,
(SELECT Id FROM MyObject1__r)
FROM MyObject2__r)
FROM Contact
However, if I am querying using "IN" operator, I am getting an error. Looking at the errors they seem intentional but I am unable to understand why such SOQL with IN operators wont work
First Example
SELECT Id FROM Contact
WHERE (
MyCustomCurrencyField__c >= 1000
)
AND (Id IN (SELECT Contact__c FROM MyObject2__r WHERE CustomBoolean__c = True))
AND (Id IN (SELECT MyObject2Field__r.Contact__c FROM MyObject1__r WHERE
customNumberField__c > 2000))
Error: The inner select field 'MyObject2Field__r.Contact__c' cannot have more than one level of relationships
Second Example
SELECT Id FROM Contact
WHERE (
MyCustomCurrencyField__c >= 1000
)
AND (Id IN (SELECT Contact__c FROM MyObject2__c WHERE CustomBoolean__c = True
AND (Id IN (SELECT MyObject2Field__c FROM MyObject1__c WHERE
customNumberField__c > 2000))
))
Error: Nesting of semi join sub-selects is not supported
I am assuming with the latest release these joins should also work. I am missing something here.