Javascript Question posted in
A very good W3school tutorial can be found here.

Javascript – Unable to order based on joined table with Many to Many relationship

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.

Tags:

2

Answers


  1. 0

    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 specific on clause and a literal order by clause. The raw query would be:

    SELECT UserModel.*
FROM Users AS UserModel
LEFT JOIN userTags on usertags.userId = UserModel.id AND usertags.tagId = 123
ORDER BY CASE WHEN usertags.tagId IS NOT NULL THEN 1 ELSE 2 END
LIMIT 30
OFFSET 0

-- note that usertags.tagId = 123 must be part of the ON clause
-- instead of the WHERE clause for left join to work as expected
-- if predicate matches then the value of usertags.tagId in
-- select/order by clause will be 123 otherwise it will be null

    In sequelize you will write the above as:

    await User.findAll({
    include: [{
        required: false, // left join
        model: Tag,
        as: 'foo', // alias makes it easier to referene in order by
        where: {
            id: 123 // this is added to the ON clause
        },
        attributes: [], // dont add to select
    }],
    order: [
        [Sequelize.literal('CASE WHEN "foo"."id" IS NOT NULL THEN 1 ELSE 2 END'), 'ASC']
    ],
    offset: 0,
    limit: 30,
});
    Login or Signup to reply.
  2. 0

    Your query does not define what UserModel is. Sequelize generates a subquery for UserModel, as it is unaware of that being an alias according to your intention. So you will need to specify the alias:

    const include = [];
const page = 1;
const rowsPerPage = 30;
const sortByTagId = 1134;

include.push(...[
  { association: User.Tags, required: true }
]);

await User.findAll({
  attributes: [
      'users', 'UserModel'
  ],
  include,
  order: [
    fn('BOOL_OR', where(col('usertags.tagId'), sortByTagId)),
    'ASC',
  ],
  group: ['UserModel.id'],
  offset: rowsPerPage * (page - 1),
  limit: rowsPerPage,
});
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search