belongsToMany eager loaded association, unexpected object

[miguelcobain]

I have the following models with a belongsToMany relation:

  var User = sequelize.define("User", {
    firstname: DataTypes.STRING,
    lastname: DataTypes.STRING,
    username: DataTypes.STRING,
    password: DataTypes.STRING
  }, {
    classMethods: {
      associate: function(models) {
        User.belongsToMany(models.Role, { as:'roles', through: 'UserRoles'} );
      }
    }
  });

  var Role = sequelize.define("Role", {
    name: DataTypes.STRING,
    description: DataTypes.STRING
  }, {
    classMethods: {
      associate: function(models) {
        Role.belongsToMany(models.User, { as:'users', through: 'UserRoles'} );
      }
    }
  });

Then I excute this and pass the result to the web framework I'm using (Hapi.js in my case):

models.User.findAll({ include: [{ model:models.Role, as:'roles' }] })

The following json resulted:

[
    {
        "id": 1,
        "firstname": "Miguel",
        "lastname": "Andrade",
        "username": "miguelcobain",
        "password": "dirtylittlesecret",
        "createdAt": "2015-01-20T12:49:40.331Z",
        "updatedAt": "2015-01-20T12:49:40.331Z",
        "roles": [
            {
                "id": 1,
                "name": "admin",
                "description": "Administrator",
                "createdAt": "2015-01-20T12:50:46.139Z",
                "updatedAt": "2015-01-20T12:50:46.139Z",
                "UserRoles": {
                    "createdAt": "2015-01-20T12:51:34.046Z",
                    "updatedAt": "2015-01-20T12:51:34.046Z",
                    "UserId": 1,
                    "RoleId": 1
                }
            }
        ]
    }
]

The SQL query that is executed is:

SELECT "User"."id",
       "User"."firstname",
       "User"."lastname",
       "User"."username",
       "User"."password",
       "User"."createdAt",
       "User"."updatedAt",
       "roles"."id" AS "roles.id",
       "roles"."name" AS "roles.name",
       "roles"."description" AS "roles.description",
       "roles"."createdAt" AS "roles.createdAt",
       "roles"."updatedAt" AS "roles.updatedAt",
       "roles.UserRoles"."createdAt" AS "roles.UserRoles.createdAt",
       "roles.UserRoles"."updatedAt" AS "roles.UserRoles.updatedAt",
       "roles.UserRoles"."UserId" AS "roles.UserRoles.UserId",
       "roles.UserRoles"."RoleId" AS "roles.UserRoles.RoleId"
FROM "Users" AS "User"
LEFT OUTER JOIN (
              "UserRoles" AS "roles.UserRoles"
              LEFT OUTER JOIN "Roles" AS "roles" ON "roles"."id" = "roles.UserRoles"."RoleId"
) ON "User"."id" = "roles.UserRoles"."UserId";

What I'm not understanding is how the UserRoles object ended up on the eager loaded roles array.

I'm using PostgreSQL, postgres dialect, "pg": "^3.6.3" and"sequelize": "^2.0.0-rc7".

[mickhansen]

That's how join table attributes are included. You can remove the completely by using include: [{ model:models.Role, as:'roles', through: {attributes: []} }]

[mickhansen]

The reason is that we don't currently distinguish between join tables that only contain foreign keys and join tables that contain actual values.

[miguelcobain]

That indeed worked, thanks for the quick reply!

I'll just leave here my POV on this matter:

• A join table which has additional values must be another model. I think it's a fair assumption to make.
• Having the join table values inside the eager loaded model isn't very expected. We could be making some nonsense (not formally correct) object tree? (i.e there isn't a one-to-one association between Role and UserRole, but the object tree seems to imply that)
• Perhaps considering a simple join table with only foreign keys should be the default.

Thanks again.

[mickhansen]

A join table which has additional values must be another model. I think it's a fair assumption to make.

Yeah i agree.

Having the join table values inside the eager loaded model isn't very expected

I agree aswell, but that's a major change that can be achieved by using the join model as an include (so not a big priority to change since it can be achieved trivially already)

[miguelcobain]

Also, there aren't any docs on this through property on includes, right?
I would document it, but I didn't understand it, honestly.

[mickhansen]

@miguelcobain hmm possibly not, we can be quite bad at not having the docs follow the code.

[himanshugogroup]

Using sequelize: "^6.36.0"
Below code works for me by having zero attribute selection in association but using it in where clause so only attribute of main model will be in select but as in sql we ca select main table attribute but where clause which run before select can use filter.

import { Op, col, fn, where } from 'sequelize';

const response = await MainModel.findOne({
                include: [
                    {
                        model: AssociationModel1, as: 'associationModel1', attributes: [], required: false
                    },
                    {
                        model: AssociationModel2, as: 'associationModel2', attributes: [], required: false
                    }
                ],
                where: {
                    joining_date: { [Op.lte]: <date_value> }, // dates in these
                    '$associationModel1.Id$': null,
                    '$associationModel2.Id$': null
                },
                attributes: ['MainModel.refId', 'MainModel.Date', [fn('COUNT', col('MainModel.Id')), 'id_count']],
                group: ['MainModel.refId', 'MainModel.Date'],
                having: where(fn('COUNT', col('MainModel.Id')), { [Op.gte]: 1 }),
            })