I am working on a react-admin project that involves a connection to an express server through a data provider.
I have not been able to get sorting or pagination to work, and I understand that I have to make changes in my express API responses to get these features to work.
I wrote the code below to detect what the sort parameter is sending, in hopes that I could have a different API response depending on the sort URL.
I have been having a really hard time getting the sort and pagination features to work. Please help if you can! TY
usersRouter.get("/", addRAHeader, checkJwt, async (req, res) => {
const user_id = req.query.sort
console.log(user_id)
if(req.query.sort === ["id","ASC"]) {
try {
connection.execute(
// getUsers,
getUsersASC,
function(err, users, fields) {
console.log("USERSDESC")
res.status(200).json(users)
}
)
} catch (err) {
res.json({message:err});
}
}
else if(req.query.sort === ["email","ASC"]) {
try {
connection.execute(
// getUsers,
getUsersASC,
function(err, users, fields) {
console.log("USERSASC")
res.status(200).json(users)
}
)
} catch (err) {
res.json({message:err});
}
} else {
try {
connection.execute(
// getUsers,
getUsersASC,
function(err, users, fields) {
console.log("USERSELSE")
res.status(200).json(users)
}
)
} catch (err) {
res.json({message:err});
}
}
});```
To begin with you can store two vars in single query variable sort. You can design your queryParams Like /?sortBy=id&sortOrder=DESC
Your express code might look like this
usersRouter.get("/", addRAHeader, checkJwt, async (req, res) => {
const user_id = req.query.sort
const sortBy = req.query.sortBy
const sortOrder = req.query.sortOrder
//if(req.query.sort === ["id","ASC"]) {
if(sortBy === "id" && sortOrder === "ASC")
try {
connection.execute(
// getUsers,
getUsersASC,
function(err, users, fields) {
console.log("USERSDESC")
res.status(200).json(users)
}
)
} catch (err) {
res.json({message:err});
}
}
// else if(req.query.sort === ["email","ASC"]) {
else if(sortBy === "email" && sortOrder === "ASC") {
try {
connection.execute(
// getUsers,
getUsersASC,
function(err, users, fields) {
console.log("USERSASC")
res.status(200).json(users)
}
)
} catch (err) {
res.json({message:err});
}
} else {
try {
connection.execute(
// getUsers,
getUsersASC,
function(err, users, fields) {
console.log("USERSELSE")
res.status(200).json(users)
}
)
} catch (err) {
res.json({message:err});
}
}
});
I was able to solve this problem. Conceptually, the thing I was missing is that react-admin uses serverside sorting and pagination.
Once I realized that, I simply wrote some code in my express API endpoint that was able to properly receive the sort and filter parameters in the URL query, and then I used a querybuilder function to create a custom MySQL query that reflected those parameters.
usersRouter.get("/", addRAHeader, checkJwt, async (req, res) => { setTimeout(() => {
const { sort, range, filter } = req.query
if (filter || range || sort !== undefined) {
parsedFilter = JSON.parse(filter).q
parsedRange = JSON.parse(range)
lowerRange = parsedRange[0]
upperRange = parsedRange[1] + 1
parsedSort = JSON.parse(sort)
querySQL(queryBuilder(`${parsedSort[0]} ${parsedSort[1]}`, `${parsedRange[0]},${(upperRange - lowerRange)}`, parsedFilter), res)
} else {
querySQL(getUsers, res)
} }, 1000) });
function queryBuilder (orderByParam, limit=10, filter='') { const query = `SELECT * FROM users Where email like '%${filter}' ORDER BY ${orderByParam} LIMIT ${limit};` // console.log("QueryBuilt:", query) return query }
let querySQL = (query, res) => {
try {
connection.execute(
query,
function(err, users, fields) {
res.status(200).json(users)
}
)
} catch (err) {
res.json({message:err});
}
}
A combination of these functions above solved the issue for me.