CODEWITHSUNDEEP
sequelize.jssubquery
SexyMF
SexyMF

Reputation: 11155

Sequelize combine 2 queries as sub where

I have 2 tables SocialPostEntity and ResearchEntity.
ResearchEntity has a field relatedId which represent the SocialPostEntity.id.
In my mapping, ResearchEntity.relatedId is not mapped, it is just an integer for abstraction reasons. no foreign key either. I want to have the following in a single query:

select * from ResearchEntity where relatedId in (select id  from SocialPostEntity);

For now, I have solved it in 2 different queries, which is not efficient at all:

const fromPosts = await SocialPostEntity.findAll({ attributes: ['id'], where: { status: 1 } });
const allRelated = await ResearchEntity.findAll({ where: { postId: fromPosts.map(x => x.id) } });

I don't want to use native SQL string query, I want to use sequlize query API.
Is that possible to combine "in" statement and select? in (select id from SocialPostEntity)?

Thanks

Upvotes: 0

Views: 597

Answers (1)

A. Rodas
A. Rodas

Reputation: 20679

This feature request is still open: https://github.com/sequelize/sequelize/issues/9509.

The documentation refers to the sequelize.literal utility to perform subqueries, which is the approach used by similar questions:

const subquery = sequelize.dialect.queryGenerator.selectQuery(SocialPostEntity.tableName, {
    attributes: ['id'],
    where: { status: 1 }
}).slice(0, -1);

const allRelated = await ResearchEntity.findAll({
    where: { postId: { [Op.in] : sequelize.literal(`( ${subquery} )`) } }
});

Upvotes: 1

Related Questions