Allow conversion of a Queryable into a SubQuery or InnerJoin

[stackasaur]

Is your feature request related to a problem? Please describe.
Under the hood, SoqlSubQuery, Soql, and InnerJoin all use SoqlBuilder. There should be a built in way to convert an instance of Soql into a SubQuery or InnerJoin so we can leverage our selector classes.

A common pain point I hear is that the current way to use SubQuery and InnerJoin is frustrating because it requires a lot of extra overhead on top of the logic already written in defined selector classes.

Describe the solution you'd like
There should either be a "results" type method on Queryable to return an InnerJoin/SubQuery, or a method on InnerJoin/SubQuery to wrap a Queryable instance

// something like this:
SOQL.subquery.of('Contacts').wraps(SOQL_Contact.query())
SOQL.innerJoin.of(SOQL_Contact.query())

// or something like this
SOQL.of(Contact.sObjectType)
  .toSubQuery('Contacts')
SOQL.of(Contact.sObjectType)
  .toInnerJoin()

edit: I now believe the ideal syntax would be to add the following methods to Queryable for conversion into SubQuery or InnerJoin

interface Queryable {
  SubQuery toSubQueryOf(String relationshipName);
  InnerJoin toInnerJoinOn(SObjectField joinOn);
}

[pgajek2]

This will be done only for SubQuery. InnerJoin requires exactly one field in the SELECT clause, and if the selector includes a few default fields, it won’t be possible to adapt it.

For SubQuery:

List<Account> accounts = 
   SOQL.of(Account.SObjectType)
       .with(Account.Name).with(SOQL.SubQuery.of('Contacts', SOQL_Contact.query().byLastName('Doe')))
       .toList();

[stackasaur]

My thought was that innerjoin would just clear out the fields and subqueries, and make the developer add in the one field. Or the method signature would just require the one field at conversion to be extra clear.

Usability wise, I'd say InnerJoin is more likely to benefit from this.

[pgajek2]

I get your point. Hmm.

Removing default fields is a must to make this work, but that is also something developers would need to be aware of, which adds extra complexity. The less they need to know, the better :D

Also, InnerJoin does not seem to be very popular from what I’ve seen, even though it is quite useful.

On top of that, the InnerJoin interface does not allow any “forbidden” clauses:

public interface InnerJoin {
    InnerJoin of(SObjectType ofObject);
    // SELECT
    InnerJoin with(SObjectField field);
    // WHERE
    InnerJoin whereAre(FilterGroup filterGroup);
    InnerJoin whereAre(Filter filter);
}

A developer using it cannot add LIMIT, OFFSET, multiple with clauses, etc. If we pass a selector instead, developers would be able to set whatever they want, which would compile successfully but fail at runtime.

This could also be an issue for SubQuery, but in the case of subqueries, the methods between Queryable and SubQuery are quite similar. With InnerJoin, we can only have one field and one condition. It could also be confusing that you can chain many methods, but they do not actually work.

Last but not least, we would have to introduce quite complex syntax for it, for example:

// new
whereAre(SOQL.Filter.with(Account.Id)
    .isIn(SOQL.InnerJoin.of(Contact.AccountId, SOQL.Contact.query.byLastName('Test'))
    .toList();
// vs
// current
whereAre(SOQL.Filter.with(Account.Id)
    .isIn(SOQL.InnerJoin.of(Contact.SObjectType)
           .with(Contact.AccountId)
           .whereAre(SOQL.Filter.with(Contact.Name).equal('Test'))
    )
    .toList();

Even though the current version is longer, I find it a little easier to understand. Hmmm.

Let me think about it more.

[stackasaur]

The more I think about it, the more I like the syntax of being able to convert a Queryable into an InnerJoin or a Subquery via

toSubQuery(String relationshipName)

toInnerJoin(SObjectFiled joinOn)

At least in that it resolves some of the syntax complexity, even if it does deviate a bit from the established syntax.

Hard to believe InnerJoin isn't more popular, so many of my queries end up with joins. This issue came up dealing with annoyances of having complex contact queries and not being able to use those selectors to query contacts.

[pgajek2]

First of all, let's figure out what would be the end result, and let's compare it with the current approach.

[stackasaur]

I'm going to use a boiled down example from one of my orgs. In this, field technicians managed via a third party network are stored as Contacts and they queries based on location as well as their ability to perform the work in question (lets call that object Skill__c).

I have a selector on Skill like this:

public class SOQL_Skill extends SOQL implements SOQL.Selector{

  // constructor not shown

  public SOQL_Skill ByType(String type){
    whereAre(Filter.with(Skill__c.Type__c).equal(type));
    return this;
  }
  public SOQL_Skill byNames(Iterable<String> names){
    whereAre(Filter.with(Skill__c.Name).isIn(names);
    return this;
  }
}

And I have a selector on Contact like this:

public class SOQL_Contact extends SOQL implements SOQL.Selector{

  // constructor not shown

  public SOQL_Contact byDistanceInMilesFrom(Location origin, Decimal mi){
    whereAre(
      Filter.with(
        SOQL.Distance.of(Contact.MailingAddress).between(origin).mi()
      ).lessThan(mi);
    return this;
  }
}

If I want to query contacts by distance with an inner join by the proper skills, I'd currentlly have to do something like this:

  Contact[] qualifiedContacts = SOQL_Contact.query()
    .byDistanceInMilesFrom(loc, 15)
    . whereAre(SOQL.Filter.id().isIn(
      SOQL.subquery.of(Skill__c.sObjectType)
        .with(Skill__c.Contact__c)
        .whereAre(Filter.with(Skill__c.Type__c).equal(type)
        .whereAre(Filter.with(Skill__c.Name).isIn(names)
    ).toList();

Where if we add Queryable.toInnerJoin(SObjectFiled joinOn)

Contact[] qualifiedContacts = SOQL_Contact.query()
    .byDistanceInMilesFrom(loc, 15)
    . whereAre(SOQL.Filter.id().isIn(
       SOQL_Skill.query()
          .byType(type)
          .byNames(names)
          .toInnerJoinOn(Skill__c.Contact__c)
    ).toList()

Now this example is pretty basic in the selector logic, but I definitely have some much larger selectors that I don't particularly like having to duplicate the logic for.

Note that I changed the signature to toInnerJoinOn(SObjectField).