Trying to use sequelize findAndCountAll method, to get items. I've to use distinct, and offset with limit due to my task.
The problem is, that in associated model i've column with array type, and i need to order parent model by that array length.
My query looks like :
const { rows, count } = await this.repo.findAndCountAll({
where: { someField: someValue },
col: 'someCol',
distinct: true,
include: [
{
model: someNestedModel,
as: 'someNestedModelAssociation',
include: [{ model: someInnerNestedModel, as: 'someInnerNestedAssociation' }]
}
],
// eslint-disable-next-line @typescript-eslint/ban-ts-comment
//@ts-ignore
order: this.getOrderByOptions(sortOrder, orderBy),
limit,
offset
});
getOrderByOptions(sortOrder, orderBy) {
switch (orderBy) {
case Sort_By_Array_Length:
return Sequelize.literal('json_array_length(someModelName.someColumnWithArrayName) ASC');
default:
return [[orderBy, sortOrder]];
}
}
The problem is, that my order by query is used both in subQuery and mainQuery. And using it into subQuery leads to error, cz there is no such field.
If i use subQuery:false flag, it works, but then i got messed with returning results, due to problems with subQuery:false and offset&limits.
So the question is, is there a way, to exclude orderBy field from subQuery?
P.S. Models have many to many association with through table.