我有这样的关系:
Clients -> ProgramsClients <- Programs
我想做的基本上是:
SELECT * FROM Programs p JOIN ProgramsClients pc on p.id = pc.programId WHERE pc.clientId = 1 LIMIT 0, 100;
我已经设法通过以下代码达到了这样的目的:
query = {
include: [{
model: models.Clients,
attributes: [],
require: true,
}],
where: { '$Clients.id$': 1 }
}
models.Programs.findAll(query) // This works
其中产生:
SELECT [...]
FROM `programs` AS `Programs` LEFT OUTER JOIN (
`ProgramsClients` AS `Clients->ProgramsClients`
INNER JOIN `clients` AS `Clients`
ON `Clients`.`id` = `Clients->ProgramsClients`.`ClientId`)
ON `Programs`.`id` = `Clients->ProgramsClients`.`ProgramId`
WHERE `Clients`.`id` = 1;
这有效,但是当我尝试限制它时,出现错误。代码:
query = {
include: [{
model: models.Clients,
attributes: [],
require: true,
}],
limit: 0,
offset: 10,
where: { '$Clients.id$': 1 }
}
models.Programs.findAll(query) // This fails
其中产生:
SELECT [...]
FROM (SELECT `Programs`.`id`, `Programs`.`name`, `Programs`.`description`, `Programs`.`createdAt`, `Programs`.`updatedAt`
FROM `programs` AS `Programs` WHERE `Clients`.`id` = 1 LIMIT 0, 10) AS `Programs`
LEFT OUTER JOIN ( `ProgramsClients` AS `Clients->ProgramsClients`
INNER JOIN `clients` AS `Clients`
ON `Clients`.`id` = `Clients->ProgramsClients`.`ClientId`)
ON `Programs`.`id` = `Clients->ProgramsClients`.`ProgramId`;
错误: DatabaseError [SequelizeDatabaseError]: Unknown column 'Clients.id' in 'where clause'
注意:我使用的是 MySQL 数据库。
有没有更简单的方法来解决这个问题并为 SQL 生成所需的(或类似的)结果?
慕少森
相关分类