Objection.js: Question: how to model ternary relationships?

[bobbypriambodo]

Thank you for this awesome library. I have a question though; suppose I have these entities:

1. Article (id, title, etc.)
2. Member (id, name, etc.)
3. VoteType (id, label, etc.)
4. ArticleVote (article_id, member_id, vote_type_id, timestamp)

With ArticleVote being the pivot table. A member can only vote once per article, so I'm having a UNIQUE index on (article_id, member_id). From what I understand, this is called a ternary relationship. My question is: how do I map this relationship to Objection.js model?

In particular, I want to be able to query (on top of my head, there can be more common use case that I missed):

1. Votes for an Article (having Member and VoteType fields)
   eg.

sql SELECT member.name, vote_type.label, timestamp FROM article_vote LEFT JOIN member ON article_vote.member_id = member.id LEFT JOIN vote_type ON article_vote.vote_type_id = vote_type.id WHERE article_vote.article_id = [id] AND possibly_some_more_conditions...

1. Vote of a Member for an Article
   (This can be done using simple search on the result of 1, would be needed in the same place anyway, so it saves DB roundtrip)
2. Articles voted by a member (having Article and VoteType fields)
   eg.

sql SELECT article.title, vote_type.label, timestamp FROM article_vote LEFT JOIN article ON article_vote.article_id = article.id LEFT JOIN vote_type ON article_vote.vote_type_id = vote_type.id WHERE article_vote.member_id = [id] AND possibly_some_more_conditions...

1. Vote count of an Article per VoteType
   eg.

sql SELECT vote_type.label, COUNT(1) FROM article_vote LEFT JOIN vote_type ON article_vote.vote_type_id = vote_type.id WHERE article_vote.article_id = [id] GROUP BY vote_type.label

1. Vote count of an Article for one type only (modifying 4)

Is there an intended way to do this on Objection.js?

Also, I am by no means an SQL expert, so suggestions on improving the query is also welcomed. My ORM knowledge is kind of rusty but I decided to try Objection.js because it has quite a nice API and wanted to see if it fits my use case. Thanks!

[koskimas]

Here are the models I would create:

// Article.js
class Article extends Model {
  static tableName = 'article';
  static relationMappings = {
    voteTypes: {
      relation: Model.ManyToManyRelation,
      modelClass: VoteType,
      join: {
        from: 'article.id',
        through: {
          modelClass: ArticleVote,
          from: 'article_vote.article_id',
          to: 'article_vote.vote_type_id'
        },
        to: 'vote_type.id'
      }
    },
    members: {
      relation: Model.ManyToManyRelation,
      modelClass: Member,
      join: {
        from: 'article.id',
        through: {
          modelClass: ArticleVote,
          from: 'article_vote.article_id',
          to: 'article_vote.member_id'
        },
        to: 'member.id'
      }
    }
  }
}

// Member.js
class Member extends Model {
  static tableName = 'member';
  static relationMappings = {
    voteTypes: {
      relation: Model.ManyToManyRelation,
      modelClass: VoteType,
      join: {
        from: 'member.id',
        through: {
          modelClass: ArticleVote,
          from: 'article_vote.member_id',
          to: 'article_vote.vote_type_id'
        },
        to: 'vote_type.id'
      }
    },
    articles: {
      relation: Model.ManyToManyRelation,
      modelClass: Article,
      join: {
        from: 'member.id',
        through: {
          modelClass: ArticleVote,
          from: 'article_vote.member_id',
          to: 'article_vote.article_id'
        },
        to: 'article.id'
      }
    }
  }
}

// VoteType.js
class VoteType extends Model {
  static tableName = 'vote_type';
  static relationMappings = {
    members: {
      relation: Model.ManyToManyRelation,
      modelClass: Member,
      join: {
        from: 'vote_type.id',
        through: {
          modelClass: ArticleVote,
          from: 'article_vote.vote_type_id',
          to: 'article_vote.member_id'
        },
        to: 'member.id'
      }
    },
    articles: {
      relation: Model.ManyToManyRelation,
      modelClass: Article,
      join: {
        from: 'vote_type.id',
        through: {
          modelClass: ArticleVote,
          from: 'article_vote.vote_type_id',
          to: 'article_vote.article_id'
        },
        to: 'article.id'
      }
    }
  }
}

// ArticleVote.js
class ArticleVote extends Model {
  static tableName = 'article_vote';
  static idColumn = ['article_id', 'member_id'];
  static relationMappings = {
    article: {
      relation: Model.BelongsToOneRelation,
      modelClass: Article,
      join: {
        from: 'article_vote.article_id',
        to: 'article.id'
      }
    },
    member: {
      relation: Model.BelongsToOneRelation,
      modelClass: Member,
      join: {
        from: 'article_vote.member_id',
        to: 'member.id'
      }
    },
    voteType: {
      relation: Model.BelongsToOneRelation,
      modelClass: VoteType,
      join: {
        from: 'article_vote.vote_type_id',
        to: 'vote_type.id'
      }
    }
  }
}

You will need to use the "require path" version of modelClass for some of the relations to prevent require loops. See this example.

Note that you don't need to create all relations in both ways. You don't need to create a model for the pivot table ArticleVote if you don't want to etc. There is very little magic in the relations. They are very explicit.

Now for the queries. The whole idea of objection is that you can always just write sql using knex's builder syntax. You don't need to learn any new DSL or "ORM way" to do things if you don't want to. For example first query could simply be written like this:

ArticleVote
  .query()
  .select('member.name', 'vote_type.label', 'timestamp')
  .leftJoin('member', 'article_vote.member_id', 'member.id')
  .leftJoin('vote_type', 'article_vote.vote_type_id', 'vote_type.id')
  .where('article_vote.article_id', id)
  .where(...)

But this is a lot of boilerplate and the whole idea if relation mapping and ORM is to reduce this boilerplate. There are couple of ways achieve the same result more easily with objection.

• Use *joinRelationmethods to avoid repeating the join keys:

ArticleVote
  .query()
  .select('member.name', 'voteType.label', 'timestamp')
  .leftJoinRelation('member')
  .leftJoinRelation('voteType')
  .where('article_id', id)
  .where(...)

• Use eager queries. This only works if you don't need to filter the result using the related tables since eager queries don't use joins. Check this blog post for more information about the eager queries.

ArticleVote
  .query()
  .eager('[member, voteType]')
  .where('article_id', id)
  .where(...)

The *JoinRelation methods return flat record rows and don't add member and voteType objects to them. They are just shortcuts for the knex *join methods. You can always combine eager with *joinRelation to get the best of both sides:

ArticleVote
  .query()
  .eager('[member, voteType]')
  .leftJoinRelation('member')
  .leftJoinRelation('voteType')
  .where('article_id', id)
  .where('member.id', blah)
  .whereIn('voteType.bleh', '<', 42)

Note that I omitted the select from the last example. You can use select with eager, but you need to be careful to also select the join columns so that the eager queries can be performed.

I didn't run the code here, or even proof read it very well, so copy-pasting to node probably won't work 😄. Did this help at all?

[bobbypriambodo]

Ah, I see. So the idea is we map the three-way relation into two relations per model.

It starts to make sense to me, thanks for your explanation! I find the blog post about eager loading very helpful, particularly about how it works and .insertWithRelated(). It might take some time for me to swallow this up, but I think I'm going in the right direction.

Again, thanks for the great work, @koskimas :) You can close this issue if you want.

[koskimas]

You're welcome. Feel free to always ask here or in the gitter chat if you have questions.

[rafaelkallis]

@koskimas the example you gave should really be in the docs under recipies!

[newhouse]

@rafaelkallis PR's like that are for sure welcome! I've contributed (directly) to the code base only by updating docs and writing some tests for things I put in the docs. Would be much appreciated I'm sure!

[rafaelkallis]

@newhouse on it!

[KrollDev]

I see the examples but how would you "insert" or relate to the ArticleVote ternary table? If you use the relationships defined in the article, vote or member one of the columns in ArticleVote will be null.

I think the question I am struggling with is: Can you do the insert/relate with Objection or is the only way to to insert/relate to the ternary table by using Knex and manually inserting/relating all three ids?

[devinivy]

You have some options that don't involve dropping down to knex. You can either insert the relationship using the ArticleVote model, or I believe you can add extras to the through relationship configuration and treat the third id as an extra (see the bottom of this section of the docs https://vincit.github.io/objection.js/api/query-builder/mutate-methods.html#relate).

Roughly:

// Insert to the table directly through its model:

await ArticleVote.query().insert({
  article_id: articleId,
  member_id: memberId,
  vote_type_id: voteTypeId
})

// Or, after adding vote_type_id to `extras` in Member's "articles" relation:

await Member.relatedQuery('articles')
  .for(memberId)
  .relate({
    id: articleId,
    vote_type_id: voteTypeId
  });