sequelize-paginate
sequelize-paginate copied to clipboard
Published
20 hours ago •
eclass
eclass
broken query on using aggregate function
Basically I need the pagination to still work just like when using .findAll However the query behaved erratically when using .paginate instead (The joins are moved over subquery, ordering options duplicated, as well as the grouping getting out of scope.
with the .paginate commented, The query defined below works okay:
const model = require("../models");
module.exports = async (user, options) => {
//console.log("get all courses");
try {
let query = {
include: [
{
model: model.Category,
nested: true,
attributes: ['id', 'name', [Sequelize.fn('count', Sequelize.col('AcademyCourseCategory->ac2->Enrollments.id')), 'countEnrollCategory']],
include: [
{
model: model.Course,
as: 'ac2',
attributes: [],
include: [
{
as: 'Enrollments',
model: model.Enrollment,
attributes: [],
where: {}
}
]
}
]
},
{
as: 'CreatorUser',
model: model.User,
nested: true,
attributes: [
'id', 'name', 'email', 'biography', 'avatar', 'status'
]
}
],
where: {
//TO DO: EXCLUDE taken courses
},
order: [
//[Sequelize.fn('count', Sequelize.col('AcademyCourseCategory->ac2->Enrollments.id')), 'DESC'],
['count_enrollment', 'DESC']
],
group: ['AcademyCourse.id', 'AcademyCourseCategory.id', 'CreatorUser.id']
};
if (options.dtStart && options.dtEnd) {
query.where.dateAdded = {
[Sequelize.Op.gte]: new Date(options.dtStart),
[Sequelize.Op.lte]: new Date(options.dtEnd)
};
}
if (user) {
if (user.role === 'Admin') {
//do nothing? don't filter
} else {
//FILTER BY LOGGED IN USER ID
query.include[0].include[0].include[0].where.userId = user.id;
}
}
if (options.orders) {
for (let i = 0; i < options.orders.length; i++) {
let order = [options.orders[i][0], options.orders[i][1]];
query.order.push(order);
}
}
if (options.limit) {
options.limit = (options.limit > 100) ? 100 : options.limit;
query.paginate = options.limit;
}
if (options.page) query.page = options.page;
//const {docs, pages, total} = await model.Course.paginate(query);
const pages=1;
const total=12;
const docs = await model.Course.findAll(query);
return {
pages: pages,
total: total,
data: docs
};
} catch (err) {
throw err;
}
};
The query produced using paginate:
SELECT "AcademyCourse".*,
"AcademyCourseCategory"."id" AS "AcademyCourseCategory.id",
"AcademyCourseCategory"."name" AS "AcademyCourseCategory.name",
count("AcademyCourseCategory->ac2->Enrollments"."id") AS "AcademyCourseCategory.countEnrollCategory",
"CreatorUser"."id" AS "CreatorUser.id",
"CreatorUser"."name" AS "CreatorUser.name",
"CreatorUser"."email" AS "CreatorUser.email",
"CreatorUser"."biography" AS "CreatorUser.biography",
"CreatorUser"."avatar" AS "CreatorUser.avatar",
"CreatorUser"."status" AS "CreatorUser.status"
FROM (SELECT "AcademyCourse"."id",
"AcademyCourse"."title",
"AcademyCourse"."slug",
"AcademyCourse"."category",
"AcademyCourse"."type",
"AcademyCourse"."subtitle",
"AcademyCourse"."description",
"AcademyCourse"."goals",
"AcademyCourse"."requirements",
"AcademyCourse"."target_audience" AS "targetAudience",
"AcademyCourse"."topic",
"AcademyCourse"."level",
"AcademyCourse"."thumbnail",
"AcademyCourse"."video",
"AcademyCourse"."language",
"AcademyCourse"."creator",
"AcademyCourse"."organization_id" AS "organizationId",
"AcademyCourse"."count_section" AS "countSection",
"AcademyCourse"."count_enrollment" AS "countEnrollment",
"AcademyCourse"."count_lesson" AS "countLesson",
"AcademyCourse"."average_rating" AS "averageRating",
"AcademyCourse"."price_tier" AS "priceTier",
"AcademyCourse"."active_marketing" AS "activeMarketing",
"AcademyCourse"."estimated_duration" AS "estimatedDuration",
"AcademyCourse"."status",
"AcademyCourse"."date_added" AS "dateAdded",
"AcademyCourse"."last_modified" AS "lastModified",
"AcademyCourse"."active_marketing" AS "PromoId",
"AcademyCourse"."active_marketing" AS "AcademyPromotionId"
FROM "public"."academy_courses" AS "AcademyCourse"
GROUP BY "AcademyCourse"."id", "AcademyCourse"."category", "AcademyCourse"."creator"
ORDER BY "AcademyCourse"."count_enrollment" DESC
LIMIT 25 OFFSET 0) AS "AcademyCourse"
LEFT OUTER JOIN "public"."academy_course_categories" AS "AcademyCourseCategory"
ON "AcademyCourse"."category" = "AcademyCourseCategory"."id"
LEFT OUTER JOIN ( "public"."academy_courses" AS "AcademyCourseCategory->ac2" INNER JOIN "public"."academy_course_enrollments" AS "AcademyCourseCategory->ac2->Enrollments" ON
"AcademyCourseCategory->ac2"."id" = "AcademyCourseCategory->ac2->Enrollments"."course_id" )
ON "AcademyCourseCategory"."id" = "AcademyCourseCategory->ac2"."category"
LEFT OUTER JOIN "public"."global_users" AS "CreatorUser" ON "AcademyCourse"."creator" = "CreatorUser"."id"
ORDER BY "countEnrollment" DESC;
The query produced using .findAll:
SELECT "AcademyCourse"."id",
"AcademyCourse"."title",
"AcademyCourse"."slug",
"AcademyCourse"."category",
"AcademyCourse"."type",
"AcademyCourse"."subtitle",
"AcademyCourse"."description",
"AcademyCourse"."goals",
"AcademyCourse"."requirements",
"AcademyCourse"."target_audience" AS "targetAudience",
"AcademyCourse"."topic",
"AcademyCourse"."level",
"AcademyCourse"."thumbnail",
"AcademyCourse"."video",
"AcademyCourse"."language",
"AcademyCourse"."creator",
"AcademyCourse"."organization_id" AS "organizationId",
"AcademyCourse"."count_section" AS "countSection",
"AcademyCourse"."count_enrollment" AS "countEnrollment",
"AcademyCourse"."count_lesson" AS "countLesson",
"AcademyCourse"."average_rating" AS "averageRating",
"AcademyCourse"."price_tier" AS "priceTier",
"AcademyCourse"."active_marketing" AS "activeMarketing",
"AcademyCourse"."estimated_duration" AS "estimatedDuration",
"AcademyCourse"."status",
"AcademyCourse"."date_added" AS "dateAdded",
"AcademyCourse"."last_modified" AS "lastModified",
"AcademyCourse"."active_marketing" AS "PromoId",
"AcademyCourse"."active_marketing" AS "AcademyPromotionId",
"AcademyCourseCategory"."id" AS "AcademyCourseCategory.id",
"AcademyCourseCategory"."name" AS "AcademyCourseCategory.name",
count("AcademyCourseCategory->ac2->Enrollments"."id") AS "AcademyCourseCategory.countEnrollCategory",
"CreatorUser"."id" AS "CreatorUser.id",
"CreatorUser"."name" AS "CreatorUser.name",
"CreatorUser"."email" AS "CreatorUser.email",
"CreatorUser"."biography" AS "CreatorUser.biography",
"CreatorUser"."avatar" AS "CreatorUser.avatar",
"CreatorUser"."status" AS "CreatorUser.status"
FROM "public"."academy_courses" AS "AcademyCourse"
LEFT OUTER JOIN "public"."academy_course_categories" AS "AcademyCourseCategory"
ON "AcademyCourse"."category" = "AcademyCourseCategory"."id"
LEFT OUTER JOIN ( "public"."academy_courses" AS "AcademyCourseCategory->ac2" INNER JOIN "public"."academy_course_enrollments" AS "AcademyCourseCategory->ac2->Enrollments" ON
"AcademyCourseCategory->ac2"."id" = "AcademyCourseCategory->ac2->Enrollments"."course_id" )
ON "AcademyCourseCategory"."id" = "AcademyCourseCategory->ac2"."category"
LEFT OUTER JOIN "public"."global_users" AS "CreatorUser" ON "AcademyCourse"."creator" = "CreatorUser"."id"
GROUP BY "AcademyCourse"."id", "AcademyCourseCategory"."id", "CreatorUser"."id"
ORDER BY "AcademyCourse"."count_enrollment" DESC;
The models are defined below: models.zip
Relationships are defined in this file: index.zip
I don't understand why the query is broken, but i think it's trying to impose limit/offset to the main table before joining with other table
from what I see, there are several things to consider:
- hasMany relations will cause the number of rows to multiply, thus making the count inaccurate. consider illustration below:
[
{
id:1
children:[
{id:'a'},{id:'b'}
]
},
{
id:2
children:[
{id:'a'},{id:'b'}
]
}
]
we expect that the count would be 2 rows, but the query behind it actually returns 2*(2 child)=4 rows. This complicates the logic for limit-offset as well because we expect it to limit only the main table but it will impose limit to the children as well (for example if we have 3*5=15 rows and we impose 7 row limit, the 1st object will have 5 children, and 2nd object will have 2 children and 3 missing)
- From point 1, the count query should be executed separately with some logic to handle the join. a. This can either be done by neglecting the include/join, but this approach will cause problem if 'where' conditions are applied to included/joined models. This will cause the count result to not align b. Other approach is to still include/join, and applying groups (i haven't given this one more thought). This count query should then use the same 'where' condition just like the main query which returns the data rows
As for handling the limit/offset, i am still at a loss of how to better tackle it
Hi, after considering manual options, somehow i managed to make it work.
Please check the manual aproach below, see if it's helpful in fixing the issue.
const model = require("../models");
module.exports = async (user, options) => {
//console.log("get all courses");
try {
let query = {
include: [
{
model: model.Category,
nested: true,
attributes: ['id', 'name', [Sequelize.fn('count', Sequelize.col('AcademyCourseCategory->ac2->Enrollments.id')), 'countEnrollCategory']],
include: [
{
model: model.Course,
as: 'ac2',
attributes: [],
include: [
{
as: 'Enrollments',
model: model.Enrollment,
attributes: [],
where: {}
}
]
}
]
},
{
as: 'CreatorUser',
model: model.User,
nested: true,
attributes: [
'id', 'name', 'email', 'biography', 'avatar', 'status'
]
},
{
as:'Price',
model:model.Price,
nested:true
}
],
where: {
status:'active'
//TO DO: EXCLUDE taken courses
},
order: [
//[Sequelize.fn('count', Sequelize.col('AcademyCourseCategory->ac2->Enrollments.id')), 'DESC'],
['count_enrollment', 'DESC']
],
group: ['AcademyCourse.id', 'AcademyCourseCategory.id', 'CreatorUser.id', 'Price.id']
};
if (options.dtStart && options.dtEnd) {
query.where.dateAdded = {
[Sequelize.Op.gte]: new Date(options.dtStart),
[Sequelize.Op.lte]: new Date(options.dtEnd)
};
}
if (user) {
if (user.role === 'Admin') {
//do nothing? don't filter
} else {
//FILTER BY LOGGED IN USER ID
query.include[0].include[0].include[0].where.userId = user.id;
}
}
if (options.orders) {
for (let i = 0; i < options.orders.length; i++) {
let order = [options.orders[i][0], options.orders[i][1]];
query.order.push(order);
}
}
if (options.limit) {
options.limit = (options.limit > 100) ? 100 : options.limit;
query.paginate = options.limit;
//query.limit = options.limit;
}else{
query.paginate=10;
}
if (options.page) query.page = options.page;
//const {docs, pages, total} = await model.Course.paginate(query);
//ALTERNATIVE SOLUTION BECAUSE LIBRARY DOESN'T WORK PER SE
if (options.limit) {
options.limit = (options.limit > 100) ? 100 : options.limit;
query.limit = options.limit;
}else{
query.limit=10;
}
query.offset=(parseInt(query.page)-1)*query.limit;
query.subQuery=false;
const result = await model.Course.findAndCountAll(query);
const docs=result.rows;
const total=result.count.length;
const pages=Math.ceil(total/options.limit);
return {
pages: pages,
total: total,
data: docs
};
} catch (err) {
throw err;
}
};
