js-data-sql DSSqlAdapter create left join for hasOne Relationships

Question

We are using js-data-sql DSSqlAdapter in our backend nodejs service. Our model definition has a hasOne Relationship defined as follows:

module.exports = {
    name: 'covariance_predictions',
    idAttribute: 'id',
    relations: {
      belongsTo: {
        assets: {
          localKey: 'asset_1_id',
          localField: 'Covariance_Predictions'
        }
      },
      hasOne: {
        currencies: {
          localField: 'currency',
          localKey: 'currency_id'
        }
      }
    }
  };

We call the adapter using:

covariancePredictions.findAll(params, {
   with: ['currencies']
})

Question

After enabling knex debugging, we figured out, that it does not use a left join statement, but a subsequent SQL query like:

sql: 'select "currencies".* from "currencies" where "id" in (?, ?, ?, ?, ?, ?, ?)' }

Does anyone has any idea how to make js-data-sql DSSqlAdapter build a left join instead? Like:

select "covariance_predictions".id, [...], "currencies".code from "covariance_predictions" left join "currencies" on "currencies".id = "covariance_predictions".currency_id;

Answer 1

I'm one of the maintainers of js-data-sql. This is currently not supported as all relations loaded via with are done so using loadingWithRelations which performs the subsequent select ... where "id" in (...) for each relation requested.

Loading hasOne and belongsTo as part of the original query should definitely be possible via a left outer join, but not an inner join as the loading of the original entity is not dependent on the existence of a relation(s).

I've created a github issue to track this change, although I'm not sure when I'll be able to make it as js-data-sql also needs to port over to extend js-data-adapter for js-data 3.0