I am trying to implement paging using mysql limit-CodePudding

If you select that category, I want to show only 9 products in that category per page. So I tried using LIMIT OFFSET as below, but it doesn't work.

Is there any other way? I need help.

const readCategory = async (categoryId, page) => {
  const categoryProducts = await myDataSource.query(
    `SELECT c.name AS category, c.level AS categoryDepth,
      JSON_ARRAYAGG(
        JSON_OBJECT(
          "name", p.name, 
          "description", p.description,
          "priceOrigin", p.price
        )
      ) AS products
    FROM products p
    JOIN category c ON c.id = p.category_id
    WHERE p.category_id = ?
    GROUP BY p.category_id
    LIMIT (?-1)*9, 9`,
    [categoryId, page]
  );
  return categoryProducts;
};

The number in front of (1-1) of LIMIT is a number indicating a page, and when that number is received as a query, it was my intention to make it possible to search from the product calculated as -1.

If you do this, you will get the following error:

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '(1-1)*9, 9' at line 21

I have 3 files and I tried again like this:

//controller.js
const readCategory = async (req, res) => {
  try {
    const { categoryId, page } = req.query;
    const categoryProducts = await productsService.readCategory(categoryId, page);
    res.status(200).json({ data: categoryProducts });
  } catch (err) {
    res.status(404).json({ message: 'ERROR' });
  }
};

//service.js
const readCategory = async (categoryId, page) => {
  const products = await productsDao.readCategory(categoryId, page);

  products.map(data => {
    data.products = JSON.parse(data.products);
  });

  return products;
};

//DAO.js
const readCategory = async (categoryId, page) => {
  const categoryProducts = await myDataSource.query(
    `SELECT c.name AS category, c.level AS categoryDepth,
      JSON_ARRAYAGG(
        JSON_OBJECT(
          "name", p.name, 
          "description", p.description,
          "priceOrigin", p.price
        )
      ) AS products
    FROM products p
    JOIN category c ON c.id = p.category_id
    WHERE p.category_id = ?
    GROUP BY p.category_id
    LIMIT ?, 9`,
    [categoryId, (page-1)*9]
  );
  return categoryProducts;
};

After that, as a postman, I tried to send a query with ?categoryId=9&page=1 after the url, but all products were searched inside "products". I want only 9 products to be viewed.

What am I doing wrong?

CodePudding user response：

MySQL doesn't support expressions with LIMIT, so you have to perform the calculation in your application code:

…
LIMIT ?, 9`,
[categoryId, (page - 1) * 9]

CodePudding user response：

It seems that MySQL doesn't allow expressions in the limit clause. You could instead calculate them on the Node.js side though:

const readCategory = async (categoryId, page) => {
  const categoryProducts = await myDataSource.query(
    `SELECT c.name AS category, c.level AS categoryDepth,
      JSON_ARRAYAGG(
        JSON_OBJECT(
          "name", p.name, 
          "description", p.description,
          "priceOrigin", p.price
        )
      ) AS products
    FROM products p
    JOIN category c ON c.id = p.category_id
    WHERE p.category_id = ?
    GROUP BY p.category_id
    LIMIT ?, 9`,
    [categoryId, (page - 1) * 9]
  );
  return categoryProducts;
};

Side note: You'd probably want to add an order by clause to your query so it doesn't rely on some arbitrary order returned by the database.