newerton
commented
5 years ago Same problem!
Open mrofia opened 5 years ago
newerton
commented
5 years ago Same problem!
mrofia
commented
5 years ago 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 35=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
mrofia
commented
5 years ago 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
commented
4 years ago you still have this problem ?
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:
The query produced using paginate:
The query produced using .findAll:
The models are defined below: models.zip
Relationships are defined in this file: index.zip