Recursive PostgreSQL query in sequelize?

Question

Here I have found an example of recursive query that fetches all subordinates of a manager:

Here is a table with test data:

INSERT INTO employees (
    employee_id,
    full_name,
    manager_id
)
VALUES
    (1, 'Michael North', NULL),
    (2, 'Megan Berry', 1),
    (3, 'Sarah Berry', 1),
    (4, 'Zoe Black', 1),
    (5, 'Tim James', 1),
    (6, 'Bella Tucker', 2),
    (7, 'Ryan Metcalfe', 2),
    (8, 'Max Mills', 2),
    (9, 'Benjamin Glover', 2),
    (10, 'Carolyn Henderson', 3),
    (11, 'Nicola Kelly', 3),
    (12, 'Alexandra Climo', 3),
    (13, 'Dominic King', 3),
    (14, 'Leonard Gray', 4),
    (15, 'Eric Rampling', 4),
    (16, 'Piers Paige', 7),
    (17, 'Ryan Henderson', 7),
    (18, 'Frank Tucker', 8),
    (19, 'Nathan Ferguson', 8),
    (20, 'Kevin Rampling', 8);

And here is a query that recursively gets all subdordinates of manager with id = 2.

WITH RECURSIVE subordinates AS (
    SELECT
        employee_id,
        manager_id,
        full_name
    FROM
        employees
    WHERE
        employee_id = 2
    UNION
        SELECT
            e.employee_id,
            e.manager_id,
            e.full_name
        FROM
            employees e
        INNER JOIN subordinates s ON s.employee_id = e.manager_id
) SELECT
    *
FROM
    subordinates;

Is there a way to implement it with sequelize with reasonable amount of raw sql insertions?

Anatoly · Accepted Answer · 2022-03-12 13:28:05Z

Sequelize is not intended to execute complex aggregating or recursive queries using models so you will end up with sequelize.query to execute such queries as is:

const subordinates = await sequelize.query('
WITH RECURSIVE subordinates AS (
    SELECT
        employee_id,
        manager_id,
        full_name
    FROM
        employees
    WHERE
        employee_id = $employee_id
    UNION
        SELECT
            e.employee_id,
            e.manager_id,
            e.full_name
        FROM
            employees e
        INNER JOIN subordinates s ON s.employee_id = e.manager_id
) SELECT
    *
FROM
    subordinates;', {
  type: QueryTypes.SELECT,
  bind: {
    employee_id: 2
  }
})

Try not to concatenate strings into a result SQL to avoid SQL injections and always try to use bind or replacements options to substitute needed values.

See raw queries

Collectives™ on Stack Overflow

Recursive PostgreSQL query in sequelize?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related