[Fixed] Pagination in Sequelize query without using Sequelize method

Issue

I have joined two tables using sequelize query and not able to get paginated data. I have seen a lot of examples adding only limit and offset in the sequelize methods. But since I am not using those methods and I do not have model for my Games table, I had to use the raw query to join my tables.
I have my query and code for pagination as follows:

exports.fetchPlayers = (req, res) => {
const { page, size } = req.query;
  const { limit, offset } = getPagination(page, size);
    db.sequelize.query('SELECT * FROM matches JOIN public."Games" ON (matches.match_id = public."Games".id) ORDER BY public."Games".id DESC', 
    {type: db.sequelize.QueryTypes.SELECT,
      limit: limit, 
      offset: offset // this is how I have added limit and offset
    })
  .then(function(matchDetails) {
    const response = getPagingData(matchDetails, page, limit);
      res.json({
          matchPlayer: response
      });
  }).catch(err => {
    res.json({status: 'failed', message: err});
}); 
};

const getPagination = (page, size) => {
const limit = size ? +size : 1;
  const offset = page ? page * limit : 0;
  return { limit, offset };
};
const getPagingData = (data, page, limit) => {
  const totalItems = data.length;
  const matches = data;
  const currentPage = page ? +page : 0;
  const totalPages = Math.ceil(totalItems / limit);

  return { totalItems, matches, totalPages, currentPage };
};

This code gives me all the data in the database. Anyone can help me out? Thanks.

Solution

You need to do the following.

Modify your raw query to

SELECT * FROM matches JOIN public."Games" ON (matches.match_id = public."Games".id) ORDER BY public."Games".id DESC LIMIT page*size, size;

example: SELECT * FROM tbl LIMIT 5,10;  # Retrieve rows 6-15

Also in sequelize,

pass page and size in replacements like

..
sequelize.query('SELECT * FROM matches JOIN public."Games" ON (matches.match_id = public."Games".id) ORDER BY public."Games".id DESC LIMIT :page * :size, :size', {
replacements: {
 page: page,
 size: size
},
type: 'SELECT'
})
..

2. For fetching the total pages available, you need to have another call to the database with exact same joins and where conditions and fetch the count. You can determine the total pages by dividing the count on page size.