Hide junction table data from JSON

[WoLfulus]

I have 2 tables that has a many-to-many association using a junction table. The junction table doesn't have any additional columns.

The setup is like this (the names are simplified just to illustrate:

define A with id and name
define B with id and name
define C with nothing (let sequelize define a_id and b_id)

A.hasMany(B, { through: C });
B.hasMany(A, { through: C });

I have an instance of a A model as "a" and from this instance I want to list all Bs associated to this instance. I do this with:

// a is an instance of A model that has id 1234
a.getBs().success(function(bs) {
    var json = bs.map(function(b) {
        return b.toJSON();
    });
    console.log(json);
});

I was expecting something like

[
  { "id": 1, "name": "first b" },
  { "id": 2, "name": "second b" },
  { "id": 3, "name": "third b" }
]

but it adds the junction table data to the result like this.

[
  { "id": 1, "name": "first b", "a_b": { "a_id": "1234", "b_id": "1" } },
  { "id": 2, "name": "second b", "a_b": { "a_id": "1234", "b_id": "2" }  },
  { "id": 3, "name": "third b", "a_b": { "a_id": "1234", "b_id": "3" }  }
]

I couldn't find any way to hide this (mick suggested passing { attributes: [] } to the getBs() function but the result keeps the "a_b" with both a and b ids and the data that I want is gone.

Is this a bug? Is there any way to hide this without having to post-process the result?

This guy on stackoverflow has the same problem:

http://stackoverflow.com/questions/26362965/prevent-junction-table-data-from-being-added-to-json-with-sequelize

[WoLfulus]

passing { joinTableAttributes: [] } solved the issue.

[mlegenhausen]

Where is this documented?

[mickhansen]

@mlegenhausen it's likely not current, ideally i'd like to change this to through: {attributes: []} to also support through: {where: {}}

[mlegenhausen]

That would be really nice :)

[radAragon]

Is this solutions already enabled? I can't figure out how to hide the IDs from junction table on the query options:

{
    attributes: [
      [sequelize.fn('sum', 'minutes'), 'sum']
    ],
    include: [
      {
        model: models.Project,
        attributes: ['id', 'name']
      },
      {
        model: models.EmployeeCalendar,
        attributes: [],
        where: {
          date: {
            $lte: filter.dateEnd,
            $gte: filter.dateIni
          }
        },
        through: {
          attributes: []
        },
        include: [
          {
            model: models.Employee,
            attributes: [],
            where: {
              email: jwtData.email
            }
          }
        ]
      }
    ]
  }

The "through" is ignored. The result is:

SELECT `TimeEntry`.*,
    `EmployeeCalendars.EmployeeCalendarTimeEntry`.`idTimeEntry`,
    `EmployeeCalendars.EmployeeCalendarTimeEntry`.`idEmployeeCalendar`
FROM (
    SELECT `TimeEntry`.`id`,
        sum('minutes') AS `sum`,
        `Project`.`id`,
        `Project`.`name`
...) AS 'TimeEntry'
...

This is my junction table declaration:

// EmployeeCalendar TimeEntry
    var EmployeeCalendar_TimeEntry = sequelize
    .define('EmployeeCalendarTimeEntry', {}, {
      timestamps: false
    });
    Models.EmployeeCalendar.belongsToMany(Models.TimeEntry, {
      through: EmployeeCalendar_TimeEntry,
      foreignKey: 'idEmployeeCalendar'
    });
    Models.TimeEntry.belongsToMany(Models.EmployeeCalendar, {
      through: EmployeeCalendar_TimeEntry,
      foreignKey: 'idTimeEntry'
    });

[sohaibfarooqi]

+1. However overriding toJson solved my issue. :)

[glebec]

@mickhansen it would be great if we could specify this as a model-wide option, or even sequelize-wide. My front-end doesn't need to know any implementation details about the SQL database underlying the back-end. Overriding toJSON is an option in the meantime, but slightly messier.

[greghart]

For anyone confused, WoLfulus's answer is correct, but specifically that option applies to the association getter.

a.getBs({ joinTableAttributes: [] })...

[escobar5]

I can't get this to work, I have a many-to-many relationship, and I have some columns in my junction table like this:

tenants.belongsToMany(users, { through: tenant_users })
users.belongsToMany(tenants, { through: tenant_users })

and my query:

    tenants.findAll(
    {
        joinTableAttributes: [],
        include: [{
          model: users,
          attributes: [],
          through: { attributes: [] },
          where: { id: usr.id }
        }]
    });

the result still contains the columns from the junction table (tenant_users)

[malyzeli]

@escobar5 not sure where the problem is in your case, but the following code does what you need.

I prefer using association aliases but it should work with model reference as well.

// model definition

Server.belongsToMany(User, { 
  as: "administrators",
  through: "server_administrator",
});

// query

Server.findAll({
  include: [
    {
      association: "administrators",
      through: {
        attributes: [],  
      },
    },
  ],
});

[malyzeli]

I would like to know if there is any way to configure this behaviour globally for whole Sequelize instance, or at least for any given association, so you don't have to specify it in every single query.

When defining new models, you can use defaultScope to hide attributes you don't need, but unfortunately that is not possible when defining associations, since apparently scope means something different there...

// we can do the following

const User = database.define(
  "user",
  {
    id: {
      type: Sequelize.INTEGER,
    },
    name: {
      type: Sequelize.TEXT,
    },
  },
  {
    defaultScope: {
      attributes: {
        exclude: ["createdAt", "updatedAt"],
      },
    },
  },
);

// but this is not available at the moment

Server.belongsToMany(User, { 
  through: "server_administrator",
  defaultScope: {
    attributes: {
      exclude: ["createdAt", "updatedAt"],
    },
  },
});

Any ideas how to achieve this?

[papb]

@malyzeli What do you mean by "since apparently scope means something different there..."?

[malyzeli]

@papb I'm pointing out that the term scope represents different functionality in the context of associations than what it means in model definition...

In model definition context

Scoping allows you to define commonly used queries that you can easily use later. Scopes can include all the same attributes as regular finders, where, include, limit etc.

class Project extends Model {}
Project.init({
  // Attributes
}, {
  defaultScope: {
    where: {
      active: true
    }
  },
  scopes: {
    activeUsers: {
      include: [
        { model: User, where: { active: true }}
      ]
    },
  }
});

source: documentation on Scopes

Setting defaultScope here means that Project.findAll() queries will include WHERE active = true by default. Querying activeUsers scope will include associated models with given params, etc.

This is something which is apparently not possible to preset on associations...

In association context

Association scopes allow you to specify default attributes when getting and setting associations - useful when implementing polymorphic associations. This scope is only invoked on the association between the two models, when using the get, set, add and create associated model functions.

Post.hasMany(Comment, {
  foreignKey: 'commentable_id',
  scope: {
    commentable: 'post'
  }
});

When calling post.getComments(), this will automatically add WHERE commentable = post. Similarly, when adding new comments to a post, commentable will automagically be set to post.

source: documentation on Scopes and Associations

This is very different functionality than scopes in model definition!

It feels like a naming collision and it might be quite confusing for new library users even though it is well described in the documentation.

Apart from that I think this implementation of polymorphic associations is bad database design and shouldn't be used - well explained for example in this article.

But that's just my opinion and fortunately that feature is optional, so you can still model your associations in a cleaner way...

Anyway the main question still remains - is there a way to set something like defaultScope when defining associations?

[Rohit-Rathore-Codezilla]

You can use
through: {
attributes: []
} to exclude junction table data.