Sequelize limit and offset incorrect placement in query

Question

I am using sequelize in nodeJs and I have this code:

Time_Sheet_Details.findAll({
include: [
    {
        model: timesheetNotesSubcon,
        required: false,
        attributes:["note","file_name", "id", "working_hrs", "timestamp", "has_screenshot", "notes_category"]
    },
    {
        model: Timesheet,
        attributes:["id","leads_id","userid"],
        include:[
            {
                model: Lead_Info, attributes:["id","fname","lname","email","hiring_coordinator_id","status"],
                where: { hiring_coordinator_id : 326},
                include:[{
                    model: adminInfoSchema,
                    required: false,
                    attributes:["admin_id","admin_fname", "admin_lname", "admin_email", "signature_contact_nos", "signature_company"],      
                }]
                
            },
            {model:Personal_Info,attributes:["userid","fname","lname","email"]}
        ],
    }],
where: { 
    reference_date: filters.reference_date
},
order:[
    ["id","DESC"]
],
offset:((1-1)*30),
limit : 30,

}).then(function(foundObject){
    willFulfillDeferred.resolve(foundObject);
});

And the result query is:

 SELECT `timesheet_details`.*, `timesheet_notes_subcons`.`note` AS
 `timesheet_notes_subcons.note`, `timesheet_notes_subcons`.`file_name` AS
 `timesheet_notes_subcons.file_name`, `timesheet_notes_subcons`.`id` AS
 `timesheet_notes_subcons.id`, `timesheet_notes_subcons`.`working_hrs` AS
 `timesheet_notes_subcons.working_hrs`, `timesheet_notes_subcons`.`timestamp` AS 
`timesheet_notes_subcons.timestamp`, `timesheet_notes_subcons`.`has_screenshot` AS 
`timesheet_notes_subcons.has_screenshot`, 
`timesheet_notes_subcons`.`notes_category` AS
 `timesheet_notes_subcons.notes_category`, `timesheet.lead`.`id` AS
 `timesheet.lead.id`, `timesheet.lead`.`fname` AS `timesheet.lead.fname`,
 `timesheet.lead`.`lname` AS `timesheet.lead.lname`,
`timesheet.lead`.`email` AS `timesheet.lead.email`,
 `timesheet.lead`.`hiring_coordinator_id` AS
 `timesheet.lead.hiring_coordinator_id`, `timesheet.lead`.`status` AS
 `timesheet.lead.status`, `timesheet.lead.admin`.`admin_id` AS
 `timesheet.lead.admin.admin_id`, `timesheet.lead.admin`.`admin_fname` AS 
`timesheet.lead.admin.admin_fname`, `timesheet.lead.admin`.`admin_lname` AS
 `timesheet.lead.admin.admin_lname`, `timesheet.lead.admin`.`admin_email` AS
 `timesheet.lead.admin.admin_email`, 
`timesheet.lead.admin`.`signature_contact_nos` AS
 `timesheet.lead.admin.signature_contact_nos`,
 `timesheet.lead.admin`.`signature_company` AS
 `timesheet.lead.admin.signature_company`, `timesheet.personal`.`userid` AS
 `timesheet.personal.userid`, `timesheet.personal`.`fname` AS
 `timesheet.personal.fname`, `timesheet.personal`.`lname` AS
 `timesheet.personal.lname`, `timesheet.personal`.`email` AS
 `timesheet.personal.email` FROM (SELECT `timesheet_details`.`id`,
 `timesheet_details`.`timesheet_id`, `timesheet_details`.`day`,
 `timesheet_details`.`total_hrs`, `timesheet_details`.`adj_hrs`,
 `timesheet_details`.`regular_rostered`, `timesheet_details`.`hrs_charged_to_client`,
 `timesheet_details`.`diff_charged_to_client`,
 `timesheet_details`.`hrs_to_be_subcon`, 
`timesheet_details`.`diff_paid_vs_adj_hrs`, `timesheet_details`.`status`,
 `timesheet_details`.`reference_date`, `timesheet`.`id` AS `timesheet.id`,
 `timesheet`.`leads_id` AS `timesheet.leads_id`, `timesheet`.`userid` AS
 `timesheet.userid` FROM `timesheet_details` AS `timesheet_details` 
LEFT OUTER JOIN `timesheet` AS `timesheet` 
ON `timesheet_details`.`timesheet_id` = `timesheet`.`id` 
WHERE (`timesheet_details`.`reference_date` >= '2016-04-23 16:00:00' 
AND `timesheet_details`.`reference_date` < '2017-05-02 15:59:59') 
ORDER BY `timesheet_details`.`id` DESC LIMIT 0, 30) AS
 `timesheet_details` LEFT OUTER JOIN `timesheet_notes_subcon` AS
 `timesheet_notes_subcons` ON `timesheet_details`.`id` =
 `timesheet_notes_subcons`.`timesheet_details_id` 
INNER JOIN `leads` AS `timesheet.lead` ON `timesheet.leads_id` =
 `timesheet.lead`.`id` AND `timesheet.lead`.`hiring_coordinator_id` = 326
 LEFT OUTER JOIN `admin` AS `timesheet.lead.admin` ON
 `timesheet.lead`.`hiring_coordinator_id` =
 `timesheet.lead.admin`.`admin_id` 
LEFT OUTER JOIN `personal` AS `timesheet.personal` ON `timesheet.userid`
 = `timesheet.personal`.`userid` ORDER BY `timesheet_details`.`id` DESC;

As you can see, the LIMIT 0, 30 is not in the end of the query. This an issue for me because that query will return nothing, and the limit and offset should be at the end of query like this:

 SELECT `timesheet_details`.*, `timesheet_notes_subcons`.`note` AS
`timesheet_notes_subcons.note`, `timesheet_notes_subcons`.`file_name` AS
 `timesheet_notes_subcons.file_name`, `timesheet_notes_subcons`.`id` AS
 `timesheet_notes_subcons.id`, `timesheet_notes_subcons`.`working_hrs` AS
 `timesheet_notes_subcons.working_hrs`,
 `timesheet_notes_subcons`.`timestamp` AS
 `timesheet_notes_subcons.timestamp`,
 `timesheet_notes_subcons`.`has_screenshot` AS
 `timesheet_notes_subcons.has_screenshot`,
 `timesheet_notes_subcons`.`notes_category` AS
 `timesheet_notes_subcons.notes_category`, `timesheet.lead`.`id` AS
 `timesheet.lead.id`, `timesheet.lead`.`fname` AS `timesheet.lead.fname`,
 `timesheet.lead`.`lname` AS `timesheet.lead.lname`,
 `timesheet.lead`.`email` AS `timesheet.lead.email`,
 `timesheet.lead`.`hiring_coordinator_id` AS
 `timesheet.lead.hiring_coordinator_id`, `timesheet.lead`.`status` AS
 `timesheet.lead.status`, `timesheet.lead.admin`.`admin_id` AS
 `timesheet.lead.admin.admin_id`, `timesheet.lead.admin`.`admin_fname` AS
 `timesheet.lead.admin.admin_fname`, `timesheet.lead.admin`.`admin_lname`
 AS `timesheet.lead.admin.admin_lname`,
 `timesheet.lead.admin`.`admin_email` AS
 `timesheet.lead.admin.admin_email`,
 `timesheet.lead.admin`.`signature_contact_nos` AS
 `timesheet.lead.admin.signature_contact_nos`,
 `timesheet.lead.admin`.`signature_company` AS
 `timesheet.lead.admin.signature_company`, `timesheet.personal`.`userid`
 AS `timesheet.personal.userid`, `timesheet.personal`.`fname` AS
 `timesheet.personal.fname`, `timesheet.personal`.`lname` AS
 `timesheet.personal.lname`, `timesheet.personal`.`email` AS
 `timesheet.personal.email` FROM (SELECT `timesheet_details`.`id`,
 `timesheet_details`.`timesheet_id`, `timesheet_details`.`day`,
 `timesheet_details`.`total_hrs`, `timesheet_details`.`adj_hrs`,
 `timesheet_details`.`regular_rostered`,
 `timesheet_details`.`hrs_charged_to_client`,
 `timesheet_details`.`diff_charged_to_client`,
 `timesheet_details`.`hrs_to_be_subcon`,
 `timesheet_details`.`diff_paid_vs_adj_hrs`,
 `timesheet_details`.`status`, `timesheet_details`.`reference_date`,
 `timesheet`.`id` AS `timesheet.id`, `timesheet`.`leads_id` AS
 `timesheet.leads_id`, `timesheet`.`userid` AS `timesheet.userid` 
FROM `timesheet_details` AS `timesheet_details` 
LEFT OUTER JOIN `timesheet` AS `timesheet` ON
 `timesheet_details`.`timesheet_id` = `timesheet`.`id` 
WHERE (`timesheet_details`.`reference_date` >= '2016-04-23 16:00:00' 
AND `timesheet_details`.`reference_date` < '2017-05-02 15:59:59') 
ORDER BY `timesheet_details`.`id` DESC) AS `timesheet_details` 
LEFT OUTER JOIN `timesheet_notes_subcon` AS `timesheet_notes_subcons` ON
 `timesheet_details`.`id` =
 `timesheet_notes_subcons`.`timesheet_details_id` INNER JOIN `leads` AS
 `timesheet.lead` ON `timesheet.leads_id` = `timesheet.lead`.`id` AND 
`timesheet.lead`.`hiring_coordinator_id` = 326 LEFT OUTER JOIN `admin` AS
 `timesheet.lead.admin` ON `timesheet.lead`.`hiring_coordinator_id` =
 `timesheet.lead.admin`.`admin_id` LEFT OUTER JOIN `personal` AS
 `timesheet.personal` ON `timesheet.userid` =
 `timesheet.personal`.`userid` ORDER BY `timesheet_details`.`id` DESC
 LIMIT 0, 30;

Is there something I am doing wrong in my code? Did I misplaced the order and limit?

Answer 1

I have examined the pagination problem with associated where clause problems in this article. https://malicaliskan.medium.com/e50dbc9de01d You can find why the problem occurs, in what conditions and what can be an easy solution to the issue in the article.

Answer 2

try to add $limit parameter on your request and sequelize will use it automatically ex: "http://localhost:3031/my-service?$limit=23"

Answer 3

LIMIT,OFFSET,PROJECTION of FIELDS and FULLTEXT SEARCH using sequilize in Node.js

        let msg = await Feedback.findAll({
            where: Sequelize.literal('MATCH (message) AGAINST (:feedback)'), // fulltext search query
            replacements: {
            feedback: req.body.text  
            },
            attributes: ['uID', 'message'], // projection of columns
            offset:1, // set the offset according your use case
            limit: 1  // limit the output
        }); 

Answer 4

got the same case, another way of doing it is to pass an array to limit.

{
include: [
    {
        model: timesheetNotesSubcon,
        required: false,
        attributes:["note","file_name", "id", "working_hrs", "timestamp", "has_screenshot", "notes_category"]
    },
    {
        model: Timesheet,
        attributes:["id","leads_id","userid"],
        include:[
            {
                model: Lead_Info, attributes:["id","fname","lname","email","hiring_coordinator_id","status"],
                where: { hiring_coordinator_id : 326},
                include:[{
                    model: adminInfoSchema,
                    required: false,
                    attributes:["admin_id","admin_fname", "admin_lname", "admin_email", "signature_contact_nos", "signature_company"],      
                }]

            },
            {model:Personal_Info,attributes:["userid","fname","lname","email"]}
        ],
    }],
where: { 
    reference_date: filters.reference_date
},
order:[
    ["id","DESC"]
],
limit : [((page-1)*limit), limit],

}

Answer 5

Need to place an order and where clause before includes.Do something like this

 user.findAll({
             offset: 5, limit: 5,
            order: [
// Will escape full_name and validate DESC against a list of valid direction parameters
['full_name', 'DESC']]
        }).then(function (result) {
})

the resulting query will be

if you want to put ordering in include then you need to place the order in include part

include: [{
                model: taskhelpers, required: true,
                order: {
                    order: '`updatedAt` ASC'
                }
           }]

for more detail check Pagination / Limiting and ordering

updated nested include and limit and order

var option = {
                offset: 5, limit: 5,
            order: [
// Will escape full_name and validate DESC against a list of valid direction parameters
['id', 'DESC']],
                attributes: [
                    'id', 'title',
                    [sequelize.Sequelize.fn('date_format', sequelize.Sequelize.col('date'), '%d-%b-%Y'), 'date']
                ],

                include: [

                    {
                        model: taskhelpers, required: true,

                        where: {
                            userId: req.params.userid,

                            $or: [
                                {
                                    status: {
                                        $eq: "1"
                                    }
                                },
                                {
                                    status: {
                                        $eq: "3"
                                    }
                                },
                            ]
                        }

                    }]
            };

now pass this option to your model parameter

tasks.findAll(options)
    .then(function (result) {
        res.send({message:result,error:null});
    })
        .catch(function (err) {
            res.send({message:null,error:err});
        })

this will be the generated query

Answer 6

Found an answer to my question, I just need to add the subQuery = false so that the limit and offset will not be evaluated to sub query. And the offset and limit is also in the end of the query.

offset:((page-1)*limit),
limit : limit,
subQuery:false