I want to create simple query with sequelize.queryGenerator and send it via "literal" to the model query to get the results. The basic idea is to generate query like this:
select * from table t1
where id in (
select id from table t2
where t1.productId = t2.productId
order by t2.validfrom desc limit 1
)
I can always use rawQuery for the whole thing but according to Sequelize - subquery in where clause it should be able to write it with typescript support. My case is however different as I reference the subquery to the same table. I created following code:
const whereSubquery: FindOptions<Pricing> = {
attributes: ['id'],
where: {
ProductId: 3,
validFrom: new Date(2021, 9, 15),
},
order: [['validFrom', 'desc']],
limit: 1,
};
const subQuery: string = this.queryGenerator
.selectQuery(Pricing.name, whereSubquery)
.slice(0, -1); // to remove the ';' from the end of the SQL
console.log(subQuery);
const result = await this.pricingRepository.findAll({
where: {
id: {
[Op.in]: SequelizeTypeScript.literal(`(${subQuery})`),
},
},
});
the console.log shows query as expected:
SELECT "id"
FROM "Pricing"
WHERE "Pricing"."ProductId" = 3
AND "Pricing"."validFrom" = '2021-10-15 14:25:59.406 +00:00'
ORDER BY "validFrom"
DESC LIMIT 1
so far so good. I will replace the static value "3" with literal t1."ProductId" but I need the table has "t2" as alias. If it is not possible via any parameter in FindOptions I can imagine to do it all with regex. Crazy but can be done.
However the "this.pricingRepository.findAll" method produces following error:
SequelizeDatabaseError: relation "Pricing" does not exist
Well, it is true that Pricing table has no relation to itself but in subqueries it is not expected nor required.
Is there any simple way or should I go directly to rawQuery and write everything manually in SQL?
