I'm running into issues with pagination when I am using Sequelize to perform multiple joins across four tables. I am unclear if there is a Sequelzie solution here or if I have to write raw MySQL. I can't seem to figure out the solution with raw MySQL either.
I have four tables joining each other: Transaction, TransactionOwner, Category, User
Multiple Users can own a single Transaction (stored in TransactionOwner as userId and transactionId pairs). Each Transaction can have one Category. The main issue here is when I join all of these tables together, I have multiple rows representing a single Transaction due to the joins with TransactionOwner. These multiple rows inflate the count of total transactions. I understand these rows are required for Sequelize to make sense of the data and return the owners for those transactions.
I would like 5 transactions, instead I receive 3 transactions because 2 transactions have more than one owner. If possible I would like the limit and offset to look at transactionId and fetch 5 unique transactionIds.
For example, there could be 8 transaction rows returned and the set of data only has 5 unique transactionIds and the additional rows are due to multiple owners for the 5 unique transactions.
I'm happy to write raw MySQL here or move away from limit/offset for pagination as long as I am able to filter or sort and paginate.
Please let me know if you need more information and thank you in advance!
Versions
Sequelize: v6.3.5
mysql2: v2.2.5
Node: v12.18.3
MySQL: v8.0.22
Sequelize Model Relationships
Transaction.associate = (models) => {
Transaction.hasOne(models.Category, {
foreignKey: "categoryId",
sourceKey: "categoryId",
});
Transaction.belongsToMany(models.User, {
through: { model: models.TransactionOwner, unique: false },
foreignKey: "transactionId",
sourceKey: "transactionId",
otherKey: "userId",
});
};
TransactionOwner.associate = (models) => {
TransactionOwner.hasOne(models.Transaction, {
foreignKey: "transactionId",
sourceKey: "transactionId",
});
TransactionOwner.hasOne(models.User, {
foreignKey: "userId",
sourceKey: "userId",
onUpdate: "CASCADE",
hooks: true,
});
};
User.associate = (models) => {
User.hasMany(models.TransactionOwner, {
foreignKey: "userId",
sourceKey: "userId",
});
};
Category.associate = (models) => {
Category.hasMany(models.Transaction, {
foreignKey: "categoryId",
sourceKey: "categoryId",
});
};
Sequelize Code
Transaction.findAndCountAll({
attributes: [
"transactionId",
"date",
"amount",
],
include: [
{
model: db.Category,
attributes: ["categoryId", "name"],
},
{
model: db.User,
attributes: ["firstName"],
through: { attributes: [] },
where: {
userId: {
[Op.in]: [28,29],
},
},
},
],
limit: 5,
offset: 0,
order: [["date, "DESC"]],
subQuery: false,
distinct: true,
});
API Results
{
"count": 752, //accurate number
"rows": [
{
"transactionId": 1727,
"date": "2020-12-31T05:00:00.000Z",
"amount": 3.8,
"category": {
"categoryId": 219,
"name": "Home"
},
"users": [
{
"firstName": "John",
}
]
},
{
"transactionId": 1725,
"date": "2020-12-30T05:00:00.000Z",
"amount": 9.75,
"category": {
"categoryId": 226,
"name": "Bubble Tea"
},
"users": [
{
"firstName": "John",
},
{
"firstName": "Jane",
}
]
},
{
"transactionId": 1726,
"date": "2020-12-30T05:00:00.000Z",
"amount": 6.19,
"category": {
"categoryId": 217,
"name": "Eating Out"
},
"users": [
{
"firstName": "Jane",
},
{
"firstName": "John",
}
]
}
],
}
SQL Code Generated by Sequelize Code
SELECT `Transaction`.`transactionId`, `Transaction`.`date`, `Transaction`.`amount`,
`category`.`categoryId` AS `category.categoryId`, `category`.`name` AS `category.name`,
`users`.`userId` AS `users.userId`, `users`.`firstName` AS `users.firstName` FROM `Transaction`
AS `Transaction` LEFT OUTER JOIN `Category` AS `category` ON `Transaction`.`categoryId` =
`category`.`categoryId` INNER JOIN ( `TransactionOwner` AS `users->TransactionOwner` INNER JOIN
`User` AS `users` ON `users`.`userId` = `users->TransactionOwner`.`userId`) ON
`Transaction`.`transactionId` = `users->TransactionOwner`.`transactionId` AND `users`.`userId` IN
(28, 29) ORDER BY `Transaction`.`date` DESC LIMIT 0, 5;
SQL Results - There are 3 unique transactionIds instead of 5 unique transactionIds
# transactionId, date, amount, category.categoryId, category.name, users.userId, users.firstName
'1725', '2020-12-30 05:00:00', '9.75', '226', 'Bubble Tea', '28', 'John'
'1725', '2020-12-30 05:00:00', '9.75', '226', 'Bubble Tea', '29', 'Jane'
'1726', '2020-12-30 05:00:00', '6.19', '217', 'Eating Out', '29', 'Jane'
'1726', '2020-12-30 05:00:00', '6.19', '217', 'Eating Out', '28', 'John'
'1727', '2020-12-31 05:00:00', '3.80', '219', 'Home', '28', 'John'
Example Desired SQL Results - There are 8 results and 5 unique transactionIds (limit 5)
# transactionId, date, amount, category.categoryId, category.name, users.userId, users.firstName
'1725', '2020-12-30 05:00:00', '9.75', '226', 'Bubble Tea', '28', 'John'
'1725', '2020-12-30 05:00:00', '9.75', '226', 'Bubble Tea', '29', 'Jane'
'1726', '2020-12-30 05:00:00', '6.19', '217', 'Eating Out', '29', 'Jane'
'1726', '2020-12-30 05:00:00', '6.19', '217', 'Eating Out', '28', 'John'
'1727', '2020-12-31 05:00:00', '3.80', '219', 'Home', '28', 'John'
'1727', '2020-12-30 05:00:00', '9.75', '226', 'Bubble Tea', '29', 'Jane'
'1729', '2020-12-30 05:00:00', '6.19', '217', 'Eating Out', '28', 'John'
'1730', '2020-12-30 05:00:00', '6.19', '217', 'Eating Out', '28', 'John'
