Objection.js: Using `through` on `HasOneRelation ` (or `BelongsToOneRelation`) possible?

[newhouse]

Hi guys and thanks again for all the great work on Objection! Loving it more and more!

I've got a situation where I have Models for Campaign, Message and Post, with corresponding tables campaigns, messages and posts.
A Campaign can have many Messages.
A Message belongs to 1 Campaign.
A Message can have many Posts.
A Post belongs to 1 Message.

So, logically, a Post has/belongs to 1 Campaign (via the Message).

Here's how I have set up the following relationMappings in the Post class:

...
campaign: {
  relation: Model.ManyToManyRelation,
  modelClass: __dirname + '/Campaign',
  join: {
    from: 'posts.message_id',
    through: {
      modelClass: __dirname + '/Message',
      from: 'messages.id',
      to: 'messages.campaign_id'
    },
    to: 'campaigns.id'
  }
}
...

When I eager('campaign') with that code in place, it works except that post.campaign is an Array of Models (length 1 of course). I'd like it to be a single Model. Here's what I've tried:

• Changing the relation to either HasOneRelation or BelongsToOneRelation results in post.campaign === null (the query uses the messages.id value against the campaigns.id for some reason).
• Adding modify: builder => { builder.first(); } to the RelationMapping has no effect.

I'm not sure what you call this type of relationship (is it _technically_ a ManyToManyRelation?), but I'm wondering if this is possibly a bug, an enhancement that can be made, or if there's a better way to do this.

Thanks for your help up front and thanks for all your hard work on Objection!

[koskimas]

This is currently not possible, but we need to make it possible! I just don't have a good idea how. Maybe we could add a isSingle: true option to the relation mapping or something like that? Then HasOneRelation would actually just be a HasManyRelation with that flag enabled.

In objection the relation types correspond to the technical implementation and not to the semantic meaning and I'd like to keep it that way. On the other hand we already have a HasOneRelation variant of the HasManyRelation so why not add a HasOneThrough relation and throw my technical implementation -reasoning to out the window 😄 What do you think?

[koskimas]

I'm starting to think a new relation type HasOneThrough is the best option. You would define the relation just like a ManyToManyRelation, but use HasOneThrough instead of ManyToManyRelation.

[devinivy]

I like that! This would be a neat feature. Would it be objection's job to ensure that it's true that the other relationships are configured so that it would really have one versus many associated records?

[newhouse]

I (_personally_) like the HasOneThrough option, as well. Sounds and feels right. Would be a good capability since this is definitely "a thing" that people will encounter and hope/expect that they can solve via a properly configured relationMappings section. 🥇

[wswoodruff]

A +1 for this feature!

[koskimas]

@devinivy Making sure of that would require implicit extra queries and I don't think that's objection's job.

For example when inserting a row to HasOneThroughRelation objection would have to check if there already is such a row in the join table. For this to always work the check would need to be done in a transaction. What if there is no transaction? Should objection start an implicit transaction? What if $beforeInsert etc. hooks start new queries, should they be part of the same transaction? The user would need to take this into consideration when writing the hooks or things would blow up.

And those are just a few examples I pulled from my hat. I think we should avoid this kind of magic like the plague in objection.

There are upsert and on duplicate key update etc. mechanisms in the database engines, but they all work differently and using them would just create a different kind of mess.

TLDR;
At least the first implementation won't take care of that.

[devinivy]

I see what you're saying– I actually meant checking the configured relationMappings() of the models involved in the relationship, rather than checking the physical database. In any case– on second thought– what I'm asking about seems like a lot of effort for not a lot in return.

[koskimas]

Oh, sorry for the rant then 😊

[newhouse]

I know the discussion over what Sami thought was being asked is now moot, but regardless and in general I love the below quote. It's a big reason I chose Objection over other "ORM"s out there. No voodoo magic is a good stance to have/take, IMO.

I think we should avoid this kind of magic like the plague in objection.

[devinivy]

Brilliant!

[uhLeeshUh]

@koskimas what was the logic for not including a BelongsToOneThrough relation? I'm looking to make a through relation for a resource (clinic) which belongs to a single other resource (market) through a join table. I'm hesitant to use the existing HasOneThrough relation since that really is only applicable to the Market model, not to Clinic.