Welcome to OStack Knowledge Sharing Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
2.4k views
in Technique[技术] by (71.8m points)

node.js - MySQL Sequelize Pagination Limit and Offset Returns Unexpected Results with Multiple Joins

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'

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Answer

0 votes
by (71.8m points)
等待大神解答

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome to OStack Knowledge Sharing Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...