I am trying to create a limit in my query which is supposed to look like this:
/api/hotels?featured=1&limit=2
When I try and execute via postman and I get this message:
"message": "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 ”2” at line 1",
And I can see that the server returns this:
Executing (default): SELECT id, name, type, city, address, distance,
photos, title, desc, rating, rooms, cheapestPrice, featured,
createdAt, updatedAt FROM hotels AS hotels WHERE hotels.featured = ‘1’
AND hotels.limit = ‘2’ LIMIT ‘2’;
My controller looks like this:
//GET ALL
export const getHotels = async (req, res, next) => {
try {
const { min, max, ...others } = req.query;
const hotels = await Hotel.findAll({
where: {
...others,
},
limit: req.query.limit,
});
res.status(200).json(hotels);
} catch (err) {
next(err);
}
};
And my Model looks like this:
const Hotel = (sequelize, Sequelize) => {
const Hotel = sequelize.define("hotels", {
name: {
type: DataTypes.STRING,
allowNull: false,
},
type: {
type: DataTypes.STRING,
allowNull: false,
},
city: {
type: DataTypes.STRING,
allowNull: false,
},
address: {
type: DataTypes.STRING,
allowNull: false,
},
distance: {
type: DataTypes.STRING,
allowNull: false,
},
photos: {
type: DataTypes.JSON,
allowNull: true,
},
title: {
type: DataTypes.STRING,
allowNull: false,
},
desc: {
type: DataTypes.TEXT,
allowNull: false,
},
rating: {
type: DataTypes.INTEGER,
min: 0,
max: 4,
},
rooms: {
type: DataTypes.JSON,
allowNull: true,
},
cheapestPrice: {
type: DataTypes.INTEGER,
allowNull: false,
},
featured: {
type: DataTypes.BOOLEAN,
default: false,
},
});
return Hotel;
};
>Solution :
limit should be in number and not string; can you try the below
please note that this code is not tested Number(req.query.limit)
export const getHotels = async (req, res, next) => {
try {
const { min, max,limit, ...others } = req.query;
const hotels = await Hotel.findAll({
where: {
...others,
},
limit: Number(limit),
});
res.status(200).json(hotels);
} catch (err) {
next(err);
}
};