How to query custom defined join table

Question

I have three models user, tab, subscription. User can subscribe to many tabs and tab can have many subscribers. user --* subscription *-- tab

Diagram from navicat

sequelize.define('subscription', {})

Tab.belongsToMany(User, { through: Subscription })
User.belongsToMany(Tab, { through: Subscription })

How do I get all subscribers for specific tab or all tabs which specific user subscribes to? I could do that in two queries but it have to be possible in one.

I have tried every possible combination of where, include, through which I would rather not share here. Every time I try to get access to subscription from any side I get subscription (subscriptions) is not associated to user.

I am trying to get something like this to work.

  await Tab.findAll({
    where: { name: ctx.params.tabname },
    include: [{
      model: Subscription,
      through: {
        where: { userUsername: ctx.params.username }
      }
    }]
  })

Please provide input and expected output, also, provide the relations between the tables. — sagi
– sagi, Commented Mar 6, 2016 at 12:20
So, given a tab, you want to list all of the other tabs that users who are subscribed to your original tab are also subscribed to? — rotten
– rotten, Commented Mar 6, 2016 at 12:20
@rotten Tab is identified by name so if I query tab by name I want to know all its subscribers. Or from the other side, user is identified by username so if I query user by username I want to know all tabs to which is he subscribing — Hnus
– Hnus, Commented Mar 6, 2016 at 12:23
@sagi Relations between tables are included in my question (code, diagram, second sentence, user --* subscription *-- tab) and for the actual data I don't think they are relevant to problem I am facing. — Hnus
– Hnus, Commented Mar 6, 2016 at 12:42

Marek Magdziak · Accepted Answer · 2016-03-07 13:10:49Z

The issue with belongsToMany is that is creates link directly to the destination entity. So to get all users for a specific tab you would have to do

Tab.findAll({
 include: [{
    model: User
  }]
});

That works when you don't really want to get any data from the subscription entity.

In a case you want to select any data from the subscription entity you need to slightly modify your relations.

Tab.hasMany(Subscription);
Subscription.belongsTo(Tab);

User.hasMany(Subscription);
Subscription.belongsTo(User);

This will create one to many relation from tab to subscription and add also binding backwards from subscription to tab. The same thing for user.

Then you can query for all subscriptions (with user info) of a specific tab or for all subscriptions (with tab info) for a specific user like following:

Tab.findAll({
  include: [{
    model: Subscription,
    include: [{
      model: User
    }]
  }]
});

User.findAll({
  include: [{
    model: Subscription,
    include: [{
      model: Tab
    }]
  }]
});

I hope that helps!

Collectives™ on Stack Overflow

How to query custom defined join table

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related