Custom relation table

[gjuchault]

Hi !

In 1-n or n-n relationships, is there any way to append custom fields to relation tables ? In multiple ORMs you can specify through: otherModel to specify the custom table to use instead of automatically creating one.

[gjuchault]

Just to clarify, this is extremly useful when precising thinkgs about relations ships. Example : User has many Rights, and I want to specify a period on this relationship.

[nfantone]

Yes! Exactly this is what I came here for.

+1.

Any updates on this?

[neumino]

Hum not really since you can work around it (I'm somehow can't find the (closed?) issue about that. You can do:

// A-B with n-n relations with extra data - that will store in C
A.hasMany(C, "links", "id", "idA")
C.hasOne(A, "a", "idA", "id")
B.hasMany(C, "links", "id", "idB")
C.hasOne(B, "a", "idB", "id")

[nfantone]

I'm confused. You say that you can work around it and yet you added the feature label yesterday? Why not close the issue?

[neumino]

Previous discussion: https://github.com/neumino/thinky/pull/122

Syntax proposed:

var A = thinky.createModel('A', {id: String});
var B = thinky.createModel('B', {id: String});
var A_B = thinky.createModel('A_B', {id: String, extra: String});
A.hasAndBelongsToMany(B, 'bs', 'id', 'id');
B.hasAndBelongsToMany(A, 'as', 'id', 'id');

var a = new A({id: 'a1'});
a.bs = [new B({id: 'b1'})];
a.saveAll();

// Stash some extra fields on the link.
A_B.get('a1_b1').update({extra: '42'});

A.get('a1').getJoin({bs: true}).then(console.log);
// > {"id": "a1", "bs": [{"id": "b1"}] }
A.get('a1').getJoin({bs: {_merge: true}}).then(console.log);
// > {"id": "a1", "bs": [{"id": "b1", "extra": "42"}] }

And

var A = thinky.createModel('A', {id: String});
var B = thinky.createModel('B', {id: String});
var Link = thinky.createModel('Link', {id: String, extra: String});
A.hasMany(B, 'bs', 'id', 'id', {through: Link});
B.hasMany(A, 'as', 'id', 'id', {through: Link});

@nfantone -- it looks like people want a shortcut for that.

[gjuchault]

I do like the { through: Model }

[gjuchault]

I'm actually working on a pull request to implement this feature. Here is the simpliest way to do it.

var thinky = require('thinky')();

var A = thinky.createModel('A', {id: String});
var B = thinky.createModel('B', {id: String});
var A_B = thinky.createModel('A_B', {id: String, extra: String});
A.hasAndBelongsToMany(B, 'bs', 'id', 'id', { through: A_B });
B.hasAndBelongsToMany(A, 'as', 'id', 'id', { through: A_B });

A.delete().then(function () {
    return B.delete();
}).then(function () {
    var a = new A({id: 'a1'});
    a.bs = [new B({id: 'b1'})];

    return a.saveAll();
}).then(function () {
    // Stash some extra fields on the link.
    return A_B.get('a1_b1').update({extra: '42'})
}).then(function () {
    return A.get('a1').getJoin({bs: true}).then(console.log);
    // { id: 'a1', bs: [ { id: 'b1', pivot: { id: 'a1_b1', extra: '42' } } ] }
});

Note : yeah, pivot should be a reserved word if we're using this technique. That's what Laravel's ORM do too.

But I thinky it'd be cool if we could do

var thinky = require('thinky')();

var A = thinky.createModel('A', {id: String});
var B = thinky.createModel('B', {id: String});
var A_B = thinky.createModel('A_B', {id: String, extra: String});
A.hasAndBelongsToMany(B, 'bs', 'id', 'id', { through: A_B });
B.hasAndBelongsToMany(A, 'as', 'id', 'id', { through: A_B });

A.delete().then(function () {
    return B.delete();
}).then(function () {
    var a = new A({id: 'a1'});
    a.bs = [new B({id: 'b1'})];

    a.bs[0].pivot.extra = '42';

    return a.saveAll();
}).then(function () {
    return A.get('a1').getJoin({bs: true}).then(console.log);
    // { id: 'a1', bs: [ { id: 'b1', pivot: { id: 'a1_b1', extra: '42' } } ] }
});

I'll see if that's possible

[neumino]

@gjuchault sent a pull request for this: https://github.com/neumino/thinky/pull/360

I always had in mind that the output would look like

{
  docFields: ...
  links: [
    {
      extraData: ...
      joinedDoc: {
        joinedDocFields: ...
     }
    }
  ]
}

I don't really have an objection to @gjuchault's proposal, it seems reasonable to me (and I kind of like it), but I was curious what other people think. Like @gjuchault mentioned n the PR, lavarel keep the data in a pivot field.

A few more questions:

• Should we keep the data in pivot field? Or nest the joined document in a "link document"
• If we use to store the data in pivot, should we use a different name? A private key? _link, link, _pivot, _extra etc.
• Should we expect people to know what the internal structure of the link document? Thinky can have a few field beside the id. Or should we just force allowExtra?

Pinging a few folks who opened similar thread: @mikefre @simonratner @bs1180 @palango @silver83

[gjuchault]

For the two first points, note that the two ways are completely opposed :

• pivot or _pivot or extra or _extra is on the related document, only when we get the main document with its relationships. This is what I tried to implement (and I'm absolutely opened to anything else)
• link or _link would be in the main document, not in the related one.

Should we expect people to know what the internal structure of the link document? Thinky can have a few field beside the id. Or should we just force allowExtra?

I'm not sure about what you mean here, as in all cases the developer must define the relation table..

I'm adding two more points here :

• First one is performance : I'm thinking about extending getJoin : if the user does not want the pivot data , maybe something like : getJoin({ B: true }) and getJoin({ B: 'extra' }) which would embed pivot data.
• Second one : I do think we should absolutely add some tests about linking the Link table with other models (« ternary association »).

Some thoughts @neumino ?

[gjuchault]

Hey @neumino, still waiting for any help/comments from you ! :)

[pyramation]

👍