how to model multiple associations to one table

[barbalex]

In my app plants are delivered to nurseries.

Deliveries can originate from nurseries or terminate there. Thus the deliveries table has two relations to the nurseries table with the corresponding fields being:

• from_nursery_id
• to_nursery_id

I can create an association for one of these in the delivery table:

static associations = {
  nursery: { type: 'belongs_to', key: 'from_nursery_id' }
}

But how can I build the second? An object cannot have two keys with the same name...

I had originally created them using other names:

static associations = {
  from_nursery: { type: 'belongs_to', key: 'from_nursery_id' },
  to_nursery: { type: 'belongs_to', key: 'to_nursery_id' }
}

Until watermelondb told me there were missing associations. Then I realized the key has to be the table name.

The same problem occurs at the other side of the relations, at the nursery table. There I originally had:

static associations = {
  from_delivery: { type: 'has_many', foreignKey: 'from_nursery_id' },
  to_delivery: { type: 'has_many', foreignKey: 'to_nursery_id' }
}

and it seems I can only add one of the two associations here too.

What will be the downsides of missing associations?

[barbalex]

I just realized I can't query nursery from delivery using Q.on for both relations because that probably looks up the delivery side field in it's associations. So I can only query the one relation.

[barbalex]

I am currently circumventing the problem by querying and filtering the data where it is needed instead of using references. I suppose that modeling multiple associations is not possible with the current implementation in WatermelonDB and will close this issue to make room for others.

[KrisLau]

@radex Is this still not possible? I'm trying to do something similar

[CDBridger]

I don't think this issue should be closed either just because it was circumvented

[KrisLau]

@radex Any updates on whether this would this be possibly added as a future feature? I think ideally it would be nice if in the model (using OP example), we are able to do something like:

  from_nursery: { type: 'belongs_to', table: 'nursery', key: 'from_nursery_id' },
  to_nursery: { type: 'belongs_to', table: 'nursery', key: 'to_nursery_id' }

Currently I'm just leaving one of my conflicting relations as a string with no relations but that leaves me without access to the super useful Q.on query

[g-nogueira]

As we are currently not able to execute joins (besides experimentalJoinTables and ), I believe the possibility to relate the same table multiple times is very important in many scenarios.

In my case, I have a "trainer_trainees" table that has the columns "trainer_id" and "trainee_id". Both columns point to User.

BTW, thank you for the great work, @radex . After trying to use RxDB, I've being enjoying very much WatermlonDB for its simplicity.

[Rodrigobanselmo]

any updates on this? same problem here!

[PEZO19]

@radex @KrisLau @Rodrigobanselmo @g-nogueira @CDBridger @barbalex

Haven't tried that yet, but maybe works. Came up with the idea myself, but maybe there is a widely known SQL pattern related to that, it feels to me there must be a "named pattern" for that. Feels similar to polymorphic types in SQL a bit, but a bit different, because here we have "edge types" instead "table types"/"node types".

When 2 entities (here: Nursery, Delivery) are "connected" with a pivot / relation, it is also possible, that they are "connected" through "multiple kind of edges" (here: "from edge"/"to edge", instead just "plain edge"). In that case, we might store this "edge type"/"edge kind" in an extra column on the relation/"pivot" table. (I prefer the word "kind" here, as type is overloaded in general.)

"Type/Kind-Extended" Relation/pivot table:

nursery_id	delivery_id	edge_kind
1	1	"from"
2	1	"to"

Given that, you can use that extra edge_kind information in your Nursery / Delivery models to filter the proper records (with Watermelon's .extend).

Edit: I made it work this way, but instead of .extend we have to use Q.experimentalJoinTables like that:

// in Delivery Model
@lazy
from_nursery = this.collections
  .get('nurseries')
  .query(
    Q.experimentalJoinTables(['pivot_nursery_delivery']), // this is necessary due nested Q.and and Q.on, otherwise we get an error message
    Q.and(
      Q.on('pivot_nursery_delivery', 'delivery_id', this.delivery_id),
      Q.on('pivot_nursery_delivery', 'edge_kind', 'from'), // todo: note this must be "to" in case of "to_nursery"
    )
  )

If I am missing something and/or you know the related SQL patterns name, please let me know.