Many-to-many (non-unique) relationships.
Hey there. I am working on creating a trivia-style website (and I am using Sequelize and MySQL). I have three data tables. A table of Users, a table of Quizzes, and a junction table (UserQuizzes). I was hoping for users to be able to take the same quiz multiple times and have all of their scores to the UserQuizzes table. Thus, I need the junction table to allow for duplicate posts of User/Quiz pairs. When defining my model relationships, I had used the following lines of code.
User.belongsToMany(Quiz, {through: 'UserQuiz'});
Quiz.belongsToMany(User, {through: 'UserQuiz'});This resulted in a UserQuiz table that did not allow for duplicate User/Quiz pairs (as the primary key of the UserQuiz table was the UserID/QuizID combination). I did some research, and I read somewhere that Sequelize does not support non-unique NxM associations (not sure if that is still the case though, so please correct me if I am wrong). Regardless, I worked around this issue by 'manually' creating the UserQuiz table like so:
sequelize.define('UserQuiz', {
id: {
type: DataTypes.INTEGER(20),
primaryKey: true,
autoIncrement: true
},
userID: {
type: DataTypes.INTEGER(11).UNSIGNED,
references: {
model: 'User',
key: 'id'
},
field: 'user_id',
allowNull: false
},
quizID: {
type: DataTypes.INTEGER(11).UNSIGNED,
references: {
model: 'Quiz',
key: 'id'
},
field: 'quiz',
allowNull: false
},
timeToComplete: {
type: DataTypes.DECIMAL(13, 2).UNSIGNED,
field: 'time_to_complete'
}This worked exactly as I had hoped. However, I am now running into some issues when trying to query the UserQuiz table. My goal is to query the table and find the five fastest times for any given Quiz. So, I used a query like this:
UserQuiz.findAll({
where: {
quizID: 2
},
limit: 5,
order: 'timeToComplete ASC'
})This also worked. However, I was also interested in pulling information from the User table as well. I read through the Sequelize docs about 'eager loading' and tried out a query like this:
UserQuiz.findAll({
where: {
quizID: 2
},
limit: 5,
order: 'timeToComplete ASC'
include: [{
model: User,
as: 'userID'
}]
})I am not able to get the 'include' statement to ever work. Could this be happening because I am no longer defining model relationships with 'belongsTo' statements? Regardless, is there any way to use NxM non-unique associations and still take advantage of eager loading? Or am I trying to pull off something that Sequelize does not currently support?
Source: sequelize/sequelize