Sequelize findAndCountAll pagination issue

Question

When using findAndCountAll with a limit and offset, I get only (for example) 8 rows per page instead of 10.

Here's what I'm using to paginate results (10 per page):

async function allPlayers(req, res) {
  const page = parseInt(req.query.page);
  const perPage = parseInt(req.query.perPage);

  const options = {
    where: {
      [Op.and]: [
        {
          type: "player",
        },
        {
          "$teams.team.type$": "club",
        },
      ],
    },
    include: [
      {
        model: UserTeam,
        duplicating: false,
        required: true,
        include: [
          {
            model: Team,
            include: [{ model: Club }, { model: School }],
          },
        ],
      },
    ],
  };

  const { rows, count } = await User.findAndCountAll({
    ...options,
    limit: perPage,
    offset: perPage * (page - 1),
  });

  res.json({ data: { rows, count } });
}

The issue seems to be Sequelize filtering out the rows when returned from SQL, instead of in the query. This happens because of this segment in the find options query:

{
  model: UserTeam,
  duplicating: false,
  required: true,
  include: [...],
}

Because of that, instead of returning 10 per paginated page, it's returning 10 or less (depending if any rows were filtered out).

Is there a fix for this behaviour or a different way to re-structure my data so I don't need this nested query?

I need this because I have a database/model structure like this:

User (players, coaches, admins, etc.)
  |
  |_ UserTeam (pivot table containing userId and teamId)
    |
    |_ Team

user_9882486 · Answer

I don't think you need to reference the pivot/join tables in the query itself. There are too many nested levels of entities in your query with School and Club etc.

If you, instead of including the nested data when you perform the query, include the User/Player model and Team model in the UserTeam model with the reference keyword, Sequelize in my experience will handle this type of operation better. The same goes for School and Club, they could be included in their join tables' model definitions instead.

Note that I use the define method and not the class based models. What if you try something like this:

    const { DataTypes } = require('sequelize')
    const sequelize = require('./../config/db')
    const User = require('./User')
    const Team = require('./Team')
    
    const UserTeam = sequelize.define('userTeam', {
      id: {
        type: DataTypes.INTEGER,
        primaryKey: true,
        autoIncrement: true,
        allowNull: false
      },
      teamId: {
        type: DataTypes.UUID,
        allowNull: false,
        unique: false,
        onDelete: 'CASCADE',
        references: {
          model: Team,
          key: 'id'
        },
        validate: {
          isUUID: {
            args: 4,
            msg: 'Team ID must be a UUID4 string.'
          }
        }
      },
      userId: {
        type: DataTypes.UUID,
        allowNull: false,
        unique: false,
        onDelete: 'CASCADE',
        references: {
          model: User,
          key: 'id'
        },
        validate: {
          isUUID: {
            args: 4,
            msg: 'User ID must be a UUID4 string.'
          }
        }
      },
      deletedAt: {
        type: DataTypes.DATE,
        allowNull: true,
        defaultValue: null
      }
    },
    {
      paranoid: true,
      tableName: 'userTeam'
    })

Then you include the Team like this:

    const users = await User.findAll({
      include: Team
    })

Associations (it would probably be more logical to call the join table TeamUser and not UserTeam):

User.belongsToMany(Team, { through: UserTeam, foreignKey: 'userId', onDelete: 'CASCADE' })
Team.belongsToMany(User, { through: UserTeam, foreignKey: 'teamId', onDelete: 'CASCADE' })

A json response based on this many-to-many relationship and the query above should result in a list of all users with every team that user is connected to. Here is one of those users:

{
    "fullName": "Player One",
    "id": "6e8ca258-9daa-4d52-b033-a077d98c29ef",
    "firstName": "Player",
    "lastName": "One",
    "email": "playerone@gmail.com",
    "password": "$2a$14$tApwpX9Ld9a1cjZMFzTGZeVEUC01M7n/tSVlldG7OEbm9sEh/k8kW",
    "verified": 1,
    "verifyCode": "4f49b5ca-12ed5a2d-1608191096291",
    "resetPasswordToken": "bd15eda5097e030988eed5d2d20b3bbb6a06439f6b9907783fc0ea19083d8410",
    "resetPasswordExpire": 1620266644543,
    "passwordChangedAt": 1608337842942,
    "createdFromIp": "122.222.10.33",
    "createdAt": "2020-12-14T06:33:13.000Z",
    "updatedAt": "2021-05-06T01:54:04.000Z",
    "deletedAt": null,
    "teams": [
        {
            "id": "427e9de4-9318-4406-aed9-fcbb3b8a3282",
            "name": "Texas Rangers",
            "type": "MLB",
            "slug": "texas-rangers",
            "createdByUserId": "6e8ca258-9daa-4d52-b033-a077d98c29ef",
            "createdAt": "2020-12-14T06:33:15.000Z",
            "updatedAt": "2020-12-29T06:07:54.000Z",
            "deletedAt": null,
            "userTeams": {
                "id": 54,
                "teamId": "427e9de4-9318-4406-aed9-fcbb3b8a3282",
                "userId": "6e8ca258-9daa-4d52-b033-a077d98c29ef",
                "deletedAt": null,
                "createdAt": "2020-12-14T06:33:15.000Z",
                "updatedAt": "2020-12-14T06:33:15.000Z"
            }
        },
        {
            "id": "cbff6df7-0e0c-4906-9e1c-54b569079d83",
            "name": "New York Yankees",
            "type": "MLB",
            "slug": "yankees",
            "createdByUserId": "16f38fc5-63d1-4285-8773-526720f9a506",
            "createdAt": "2020-12-14T23:57:11.000Z",
            "updatedAt": "2021-07-06T06:08:35.000Z",
            "deletedAt": null,
            "userTeams": {
                "id": 55,
                "teamId": "cbff6df7-0e0c-4906-9e1c-54b569079d83",
                "userId": "6e8ca258-9daa-4d52-b033-a077d98c29ef",
                "deletedAt": null,
                "createdAt": "2020-12-14T23:57:11.000Z",
                "updatedAt": "2020-12-14T23:57:11.000Z"
            }
        }
    ]
}

user_2805682 · Answer

Let try this function bro

paginate: ({
        currentPage,
        pageSize
    }) => {
        const offset = parseInt((currentPage - 1) * pageSize, 10);
        const limit = parseInt(pageSize, 10);
        return {
            offset,
            limit,
        };
    },

    // import function here
    const result = await city.findAndCountAll({
        where: conditions,
        order: [
            ['createdAt', 'DESC']
        ],
        ...paginate({
            currentPage: page,
            pageSize: limit
        }),
    })

Sequelize findAndCountAll pagination issue

2 Answers