Skip to content

Instantly share code, notes, and snippets.

@mx781
Last active January 12, 2024 16:21
Show Gist options
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save mx781/dc6b86fad71c394872834397c3f8a52f to your computer and use it in GitHub Desktop.
Save mx781/dc6b86fad71c394872834397c3f8a52f to your computer and use it in GitHub Desktop.
var Sequelize = require('sequelize');
var sequelize = new Sequelize(
'mysql://root:root@localhost:3306/sandbox',
{
dialect: 'mysql',
}
);
var User = sequelize.define('User', {
name: { type: Sequelize.STRING },
});
var Resource = sequelize.define('Resource', {
name: { type: Sequelize.STRING },
});
var Booking = sequelize.define('Booking', {
name: { type: Sequelize.STRING },
});
BookedResource = sequelize.define('BookedResource', {
id: { type: Sequelize.INTEGER, primaryKey: true, autoIncrement: true },
ResourceId: { type: Sequelize.INTEGER },
BookingId: { type: Sequelize.INTEGER }
});
User.hasMany(Booking);
Booking.belongsToMany(Resource, { through: { model: BookedResource, unique: false}, foreignKey: 'BookingId' });
Resource.belongsToMany(Booking, { through: { model: BookedResource, unique: false}, foreignKey: 'ResourceId' });
sequelize.sync({force: true})
.then(() => {
return Sequelize.Promise.join(
User.create({"name": "Armando"}),
Booking.create({name: "booking yowza"}),
Resource.create({name: "item1"})
)
.spread((user, booking, resource) =>
Sequelize.Promise.join(
booking.addResource([resource, resource]),
user.addBooking(booking)
)
)
.spread((booking, user) =>
Sequelize.Promise.join(
User.findAll({include: [{model: Booking, include: Resource}]}),
User.findAll({include: [{model: Booking, include: Resource}], raw: true})
)
)
.spread((users, usersRaw) =>
{
console.log(users.map(u => u.toJSON())[0].Bookings[0].Resources);
console.log(usersRaw);
sequelize.close()
})
})
.catch(err => {
console.log(err);
sequelize.close();
});
/*
Output:
Regular query (undesirable behavior, since Resources.length=1)
[ { id: 1,
name: 'item1',
createdAt: 2017-07-01T23:09:28.000Z,
updatedAt: 2017-07-01T23:09:28.000Z,
BookedResource:
{ id: 2,
ResourceId: 1,
BookingId: 1,
createdAt: 2017-07-01T23:09:28.000Z,
updatedAt: 2017-07-01T23:09:28.000Z } } ]
Raw query (desired outcome, since both Resources come up with the two distinct BookedResource id's):
[ { id: 1,
name: 'Armando',
createdAt: 2017-07-01T23:09:28.000Z,
updatedAt: 2017-07-01T23:09:28.000Z,
'Bookings.id': 1,
'Bookings.name': 'booking yowza',
'Bookings.createdAt': 2017-07-01T23:09:28.000Z,
'Bookings.updatedAt': 2017-07-01T23:09:28.000Z,
'Bookings.UserId': 1,
'Bookings.Resources.id': 1,
'Bookings.Resources.name': 'item1',
'Bookings.Resources.createdAt': 2017-07-01T23:09:28.000Z,
'Bookings.Resources.updatedAt': 2017-07-01T23:09:28.000Z,
'Bookings.Resources.BookedResource.id': 1,
'Bookings.Resources.BookedResource.ResourceId': 1,
'Bookings.Resources.BookedResource.BookingId': 1,
'Bookings.Resources.BookedResource.createdAt': 2017-07-01T23:09:28.000Z,
'Bookings.Resources.BookedResource.updatedAt': 2017-07-01T23:09:28.000Z },
{ id: 1,
name: 'Armando',
createdAt: 2017-07-01T23:09:28.000Z,
updatedAt: 2017-07-01T23:09:28.000Z,
'Bookings.id': 1,
'Bookings.name': 'booking yowza',
'Bookings.createdAt': 2017-07-01T23:09:28.000Z,
'Bookings.updatedAt': 2017-07-01T23:09:28.000Z,
'Bookings.UserId': 1,
'Bookings.Resources.id': 1,
'Bookings.Resources.name': 'item1',
'Bookings.Resources.createdAt': 2017-07-01T23:09:28.000Z,
'Bookings.Resources.updatedAt': 2017-07-01T23:09:28.000Z,
'Bookings.Resources.BookedResource.id': 2,
'Bookings.Resources.BookedResource.ResourceId': 1,
'Bookings.Resources.BookedResource.BookingId': 1,
'Bookings.Resources.BookedResource.createdAt': 2017-07-01T23:09:28.000Z,
'Bookings.Resources.BookedResource.updatedAt': 2017-07-01T23:09:28.000Z } ]
*/
@abhi5658
Copy link

This can probably solve the issue

User.hasMany(Booking);
Booking.belongsToMany(Resource, { through: { model: BookedResource, unique: false }, foreignKey: 'BookingId' });
Resource.belongsToMany(Booking, { through: { model: BookedResource, unique: false }, foreignKey: 'ResourceId' });
Booking.hasMany(BookedResource);
BookedResource.belongsTo(Resource, { foreignKey: 'ResourceId' });
BookedResource.belongsTo(Booking, { foreignKey: 'BookingId' });

sequelize.sync({ force: true, logging: false })
    .then(async () => {
        const [user, booking, resource] = await Promise.all([
            User.create({ "name": "Armando" }),
            Booking.create({ name: "booking yowza" }),
            Resource.create({ name: "item1" })
        ]);
        const [booking_1, user_1] = await Promise.all([
            booking.addResource([resource, resource]),
            user.addBooking(booking)
        ]);
        const [users, users1, usersRaw] = await Promise.all([
            User.findAll({
                include: [{
                    model: Booking, // belongsToMany <---------------------
                    include: [{
                        model: Resource,
                        through: { attributes: [] }
                    }],
                }]
            }),
            User.findAll({
                include: [{
                    model: Booking, // hasMany <---------------------
                    include: [{
                        model: BookedResource, // belongsTo <---------------------
                        // attributes:['id'], 
                        include: [{
                            model: Resource,
                        }],
                    }],
                }]
            }),
            User.findAll({ include: [{ model: Booking, include: Resource }], raw: true })
        ]);
        // console.log(users.map(u => u.toJSON())[0].Bookings[0].Resources);
        console.log('users', JSON.stringify(users, null, 2));
        console.log('users1', JSON.stringify(users1, null, 2)); //<---- modified findAll
        // console.log(usersRaw);
        sequelize.close();
    })
    .catch(err => {
        console.log(err);
        sequelize.close();
    });

Output:

users [ 
  {
    "id": 1,
    "name": "Armando",
    "createdAt": "2021-05-19T11:02:31.844Z",
    "updatedAt": "2021-05-19T11:02:31.844Z",
    "Bookings": [
      {
        "id": 1,
        "name": "booking yowza",
        "createdAt": "2021-05-19T11:02:31.846Z",
        "updatedAt": "2021-05-19T11:02:31.942Z",
        "UserId": 1,
        "Resources": [ // belongsToMany
          {
            "id": 1,
            "name": "item1",
            "createdAt": "2021-05-19T11:02:31.846Z",
            "updatedAt": "2021-05-19T11:02:31.846Z"
          }
        ]
      }
    ]
  }
]
users1 [
  {
    "id": 1,
    "name": "Armando",
    "createdAt": "2021-05-19T11:02:31.844Z",
    "updatedAt": "2021-05-19T11:02:31.844Z",
    "Bookings": [
      {
        "id": 1,
        "name": "booking yowza",
        "createdAt": "2021-05-19T11:02:31.846Z",
        "updatedAt": "2021-05-19T11:02:31.942Z",
        "UserId": 1,
        "BookedResources": [ // hasMany + belongsTo -> need to reformat array
          {
            "id": 1,
            "ResourceId": 1,
            "BookingId": 1,
            "createdAt": "2021-05-19T11:02:31.989Z",
            "updatedAt": "2021-05-19T11:02:31.989Z",
            "Resource": {
              "id": 1,
              "name": "item1",
              "createdAt": "2021-05-19T11:02:31.846Z",
              "updatedAt": "2021-05-19T11:02:31.846Z"
            }
          },
          {
            "id": 2,
            "ResourceId": 1,
            "BookingId": 1,
            "createdAt": "2021-05-19T11:02:31.989Z",
            "updatedAt": "2021-05-19T11:02:31.989Z",
            "Resource": {
              "id": 1,
              "name": "item1",
              "createdAt": "2021-05-19T11:02:31.846Z",
              "updatedAt": "2021-05-19T11:02:31.846Z"
            }
          }
        ]
      }
    ]
  }
]

@phdgame
Copy link

phdgame commented Jan 12, 2024

User.create({ "name": "Armando" }),
            Booking.create({ name: "booking yowza" }),

Olá, eu fiz dessa forma e funcionou, obrigado!

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment