I am trying to paginate my queries but there are raw queries. So I don't know how to do it. So far I have seen two solutions: use LIMIT but from what I have read it is not a reliable method. The second is to use FETCH but that is not a good solution either.
Im using node + graphql + sequelize.
This is my query:
const myQuery = db.sequelize.query(
`
SELECT alert.CodeDescription as AlertTypeDesc,
employee.EmployeeId,
concat(employee.LastName, ' ', employee.FirstName) as EmployeeData,
IFNULL(alert.StartDate,'') as UTCStartMomentTS,
IFNULL(alert.EndDate,'') as UTCStopMomentTS,
CASE WHEN year(alert.EndDate) IS NOT NULL
THEN SEC_TO_TIME(TIMESTAMPDIFF(second, alert.StartDate, alert.EndDate))
ELSE SEC_TO_TIME(TIMESTAMPDIFF(second, alert.StartDate, current_timestamp()))
END AS Duration,
CASE WHEN alert.EndDate IS NULL
THEN 'Open'
ELSE 'Closed'
END AS Status
FROM \`App.ScheduleAlert\` alert
INNER JOIN \`Engine.Employee\` employee
ON alert.EmployeeKey = employee.EmployeeExternalKey
INNER JOIN \`Engine.EmployeeAssignment\` employee_asg
ON employee.EmployeeId = employee_asg.EmployeeId
WHERE employee_asg.SupervisorEmployeeId = $1
AND alert.NominalDate = CURRENT_DATE()
`,
{
bind: [employeeId],
type: QueryTypes.SELECT
},
);
Has anyone ever done pagination like this, or know of any examples to look at? I can't find anything like it. I believe the best way to do it is to transform this query, and all other queries like it, into a sequelize query and use limit and offset which are more reliable than SQL limit. But before taking this way I want to know if there is any solution for this problem.
