Sequelize limit and offset incorrect placement in query

334
May 02, 2017, at 09:13 AM

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

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

READ ALSO
Mapping data (enums)

Mapping data (enums)

This seems to me like a good interview question so I'm kinda ashamed for asking someone else and not trying to figure it out, but databases is not my fieldA colleague at work wanted to have fully normalized database schema for mapping enums

278
module is not defined, exports is not defined - with typescript + react

module is not defined, exports is not defined - with typescript + react

I have a very simple app using ASP 4/MVC 5, and Typescript (23

1407
Cannot get touch response outside of soft keyboard

Cannot get touch response outside of soft keyboard

Text component works well on my other situation but things is strange in this page, it behaves as : I cannot get touch response outside of soft keyboard, I just could touch soft keyboard, what happened ? My render method :

308
How to add marker using d3js

How to add marker using d3js

How to add marker to svg fileIs it even possible to display markers when user clik button ? Any help would be really appreciated

376