I’m trying to order a set of paged results based on the existence of many to many relationship. I can write a query by hand that does this, but I’m trying to figure out how to do this the sequelize way.
My Code:
const include = [];
const page = 1;
const rowsPerPage = 30;
const sortByTagId = 1134;
include.concat([
{ association: User.Tags },
{ association: User.Location },
{ association: User.Privileges }
]);
await User.findAll({
include,
order: [
fn('bool_or', literal(`"userTags"."tagId" = ${sortByTagId}`)),
'ASC',
],
group: ['UserModel.id'],
offset: rowsPerPage * (page - 1),
limit: rowsPerPage,
});
Sequelize then produces the following query:
SELECT
"id",
"createdAt",
"deletedAt",
"updatedAt",
"firstName",
"lastName",
"suffix",
"email",
"phone"
FROM "users" AS "UserModel"
WHERE ("WorkerModel"."deletedAt" IS NULL)
GROUP BY "WorkerModel"."id"
ORDER BY bool_or("userTags"."tagId" = 1134) ASC
LIMIT 30
OFFSET 0;
This query is invalid and produces the following error:
missing FROM-clause entry for table "userTags"
I suspect sequelize is omitting my association because it doesn’t see any references to it but is ignoring my call to literal()
I suspect that I might be able to solve this problem by getting rid of literal()
but I haven’t been able to figure out how to do so while still specifying a value for tagId
.
I’ve tried experimenting with raw: true
but this doesn’t help in any way.
FYI: The code above is an approximation of something more complex from a proprietary codebase, I’ve modified it to make it easier to read and not proprietary. I apologize for any mistakes I might have made and will try to edit accordingly if necessary.