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.push(...[
{ association: User.Tags }
]);
await User.findAll({
include,
order: [
fn('BOOL_OR', where(col('usertags.tagId'), sortByTagId)),
'ASC',
],
group: ['UserModel.id'],
offset: rowsPerPage * (page - 1),
limit: rowsPerPage,
});
Sequelize then produces the following query:
SELECT
"UserModel".*,
"usertags"."id" AS "_0",
"usertags"."uuid" AS "_1",
"usertags"."createdAt" AS "_2",
"usertags"."deletedAt" AS "_3",
"usertags"."updatedAt" AS "_4",
"usertags"."tagId" AS "_6",
"usertags"."userId" AS "_8"
FROM (
SELECT
"UserModel"."id",
"UserModel"."uuid",
"UserModel"."createdAt",
"UserModel"."deletedAt",
"UserModel"."updatedAt",
"UserModel"."status",
"UserModel"."locationId",
"UserModel"."firstName",
"UserModel"."lastName",
"UserModel"."suffix",
"UserModel"."email",
"UserModel"."phone"
FROM "users" AS "UserModel"
WHERE ("UserModel"."deletedAt" IS NULL)
AND (
SELECT "userId"
FROM "usertags" AS "usertags"
WHERE (
"usertags"."deletedAt" IS NULL
AND "usertags"."userId" = "UserModel"."id"
)
LIMIT 1
) IS NOT NULL
GROUP BY "UserModel"."id"
ORDER BY bool_or("usertags"."tagId" = 1134) ASC LIMIT 30 OFFSET 0
) AS "UserModel"
INNER JOIN "usertags" AS "usertags" ON "UserModel"."id" = "usertags"."userId" AND "usertags"."deletedAt" IS NULL
ORDER BY bool_or("usertags"."tagId" = 1134) ASC;
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.
Edit: I’m targeting a query like this (tested directly against a database):
SELECT
-- string_agg(CAST("usertags"."userTagId" as varchar(255)), ','),
"UserModel".*
FROM "Users" AS "UserModel"
INNER JOIN "userTags" on "usertags"."userId" = "UserModel"."id"
WHERE ("UserModel"."deletedAt" IS NULL)
GROUP BY "UserModel"."id"
ORDER BY bool_or("usertags"."tagId" = 1134) DESC
LIMIT 30 OFFSET 0;
This will return all users ordered by if they have a particular tag. The comment is included for diagnostic purposes.
2
Answers
I am assuming that you want to select some users and sort those users first that have a specific tag. For that, you can use
left join
with a specificon
clause and a literalorder by
clause. The raw query would be:In sequelize you will write the above as:
Your query does not define what
UserModel
is. Sequelize generates a subquery forUserModel
, as it is unaware of that being an alias according to your intention. So you will need to specify the alias: