Sequelize limit and offset incorrect placement in query

Yassi picture Yassi · May 2, 2017 · Viewed 27.5k times · Source

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

Yassi picture Yassi · May 2, 2017

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