英文:
Building Sequelize JOIN table SQL queries with WHERE table alias
问题
假设我们可以使用Sequelize提供的`QueryInterface`，我们如何在`Where`子句中定义表别名？
const queryInterface = this.sequelize.getQueryInterface();
const queryGenerator = queryInterface.queryGenerator as SequelizeQueryGenerator;
const whereObject = [
{
['venueid']: {
[Op.lt]: 50
}
},
{
['e.eventid']: { // 尝试在列上定义表别名
[Op.eq]: 100
}
}
];
const sql = queryGenerator.selectQuery(
[
['venue', 'v'],
['event', 'e']
],
{ where: whereObject },
null
);
实际输出:
SELECT * FROM "venue" AS "v", "event" AS "e" WHERE ("v"."venueid" < 50 AND "v"."e.eventid" = 100);
期望输出:
SELECT * FROM "venue" AS "v", "event" AS "e" WHERE ("v"."venueid" < 50 AND "e"."eventid" = 100);
我尝试使其工作的方法是在`whereObject`中直接提供别名
{
['e.eventid']: { // 尝试在列上定义表别名
[Op.eq]: 100
}
}
不使用模型进行查询的原因是我试图将其创建为一个无模型的查询服务。
英文:
Suppose we could use QueryInterface provided by sequelize, how can we define the Table Alias in the Where clause?
const queryInterface = this.sequelize.getQueryInterface();
const queryGenerator = queryInterface.queryGenerator as SequelizeQueryGenerator;
const whereObject = [
{
['venueid']: {
[Op.lt]: 50
}
},
{
['e.eventid']: { // Attempt to define Table Alias on column
[Op.eq]: 100
}
}
];
const sql = queryGenerator.selectQuery(
[
['venue', 'v'],
['event', 'e']
],
{ where: whereObject },
null
);
Actual output:
SELECT * FROM "venue" AS "v", "event" AS "e" WHERE ("v"."venueid" < 50 AND "v"."e.eventid" = 100);
Expected output:
SELECT * FROM "venue" AS "v", "event" AS "e" WHERE ("v"."venueid" < 50 AND "e"."eventid" = 100);
My attempts to make this work is by providing the Alias directly in the whereObject
{
['e.eventid']: { // Attempt to define Table Alias on column
[Op.eq]: 100
}
}
The reason for not using a Model for query is that I'm trying to make this a model-less query service.
答案1
得分: 0
If you use the where function, it seems to work.
如果您使用`where`函数，它似乎可以工作。
const whereObject = sequelize.and(
sequelize.where(Sequelize.col('v.venue_id'), Op.lt, 50),
sequelize.where(Sequelize.col('e.event_id'), 100)
)
英文:
If you use the where function, it seems to work.
const whereObject = sequelize.and(
sequelize.where(Sequelize.col('v.venue_id'), Op.lt, 50),
sequelize.where(Sequelize.col('e.event_id'), 100)
)
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论