Cannot use grouping function
Opened this issue · 0 comments
mrofia commented
Basically I need the pagination to still work just like when using .findAll
However the extension seem to not support group functions.
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.pageSize= options.limit;
}else{
query.pageSize= 10;
}
if (options.page) query.pageIndex= options.page;
//return await model.Course.paginate(query);
return await model.Course.findAll(query);
} 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",
"Price"."id" AS "Price.id",
"Price"."name" AS "Price.name",
"Price"."pricing" AS "Price.pricing",
"Price"."date_added" AS "Price.dateAdded",
"Price"."last_modified" AS "Price.lastModified"
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"
WHERE "AcademyCourse"."status" = 'active'
ORDER BY "AcademyCourse"."id" ASC
LIMIT 10 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"
LEFT OUTER JOIN "public"."academy_price_tier" AS "Price" ON "AcademyCourse"."priceTier" = "Price"."id"
ORDER BY "AcademyCourse"."id" ASC;
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. I haven't put simple pagination code into it.
models.zip
Relationships are defined in this file:
index.zip