broken query on using aggregate function

[mrofia]

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

[newerton]

Same problem!

[mrofia]

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:

1. 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)

2. 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

[mrofia]

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;

    }

};

[deleteeeeeeeeeeeed]

you still have this problem ?