Sequelize limit and offset incorrect placement in query
Solution 1
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
Solution 2
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
Solution 3
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],
}
Related videos on Youtube
Yassi
Updated on October 12, 2021Comments
-
Yassi over 2 years
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?
-
Maria Ines Parnisari about 7 years1. Can you try removing the
include
attribute? 2. What did you mean when you saidthat query will return nothing
? If you're not limiting the results the query should return something... -
Yassi about 7 years@MariaInesParnisari Hi. Thanks for the response. Can't remove the include. I need all the attributes inside it. What I mean by it returns nothing is because of the limit before the query ends. I need the limit to be at the end of the query, not before.
-
Maria Ines Parnisari about 7 yearsAh lol I see, I did not see the entire result query.
-
Maria Ines Parnisari about 7 yearsYou could post your issue here: github.com/sequelize/sequelize/issues. You're more likely to get a fast response
-
Adiii about 7 years@Yassi its work for me
-
Yassi about 7 years@MariaInesParnisari It's ok :D yup, will post on their github as well, thanks for the suggestion.
-
-
Yassi about 7 yearsThanks for the example but I also tried putting the limit and offset in the beginning of the code, before that include but still no luck. On your example, I think it will work fine because it's not nested and does not have multiple inner joins?
-
Adiii about 7 yearsDo you require your model before include them?