CODEWITHSUNDEEP
javascriptsqlnode.jssequelize.js
eri
eri

Reputation: 3504

Sequelize select from select

I want join and filter raw query

const projects = await sequelize.query('SELECT * FROM projects + SQL MAGIC', {
  model: Projects,
  mapToModel: true,
  type: QueryTypes.SELECT,
});

In this query replace projects table with select+magic:

const dynamic_where = {id: 1}

const projects = await Projects.findAll(
  where: { ..dynamic_where },
  include: [{ model: Organization }],
)

So generated query shall become

SELECT fields,... FROM (SELECT * FROM projects + SQL MAGIC) JOIN organization WHERE organization.id = 1;

bind not suitable because of dynamic_where can contain different number of fields.

Upvotes: 0

Views: 246

Answers (1)

Emma
Emma

Reputation: 9308

If you need to modify FROM part, I think you need to use a little more low level access to Sequelize.

There is a function queryGenerator.selectQuery however this takes string as FROM table name meaning if I do

selectQuery('(...SQL MAGIC)', options, Projects)

This will generate a query string as

SELECT ... FROM '(...SQL MAGIC)' ...

FROM query is taken as a string value which is not a valid SQL.

So, a little hacky workaround.

const customQuery = selectQuery('FROM_TO_BE_REPLACED', options, Projects)
// Use JS string replace to add dynamic SQL for FROM.
// If it is Postgres, double quotes are added by queryGenerator.
// If MySQL, it would be ``
customQuery.replace('"FROM_TO_BE_REPLACED"', '(...SQL MAGIC)')

All in action.

const Model = require("sequelize/lib/model");

const parentOptions = {
  where: {
    id: 1,
    key: 'value'
  },
  include: [Organization]
};

// This is required when the inline query has `include` options, this 1 line make sure to serialize the query correctly.
Model._validateIncludedElements.bind(Projects)(parentOptions);

const customQuery = sequelize.getQueryInterface()
                     .queryGenerator
                     .selectQuery('FROM_TO_BE_REPLACED', parentOptions, Projects);

const fromQuery = '(SELECT * FROM SQL MAGIC)';

const projects = await sequelize.query(customQuery.replace('"FROM_TO_BE_REPLACED"', fromQuery), 
  {
    type: QueryTypes.SELECT
  }
);

Upvotes: 1

Related Questions