使用 where 和 limit 查询多对多关系

我有这样的关系:


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 生成所需的(或类似的)结果?


繁华开满天机
浏览 111回答 1
1回答

慕少森

我停了一下。当我回来时,我设法解决了它。基本上,我误读了文档中的超级多对多部分。您可以简单地定义与关联表(在本例中为 ProgramsClients)的一对多关系(即使您使用的是多对多关系),然后包含 ProgramsClients 并执行任何您想要的操作。(您必须为此声明 ProgramsClients 的 id 列)。query = {&nbsp; &nbsp;include: [{&nbsp; &nbsp; &nbsp; &nbsp;model: models.ProgramsClients,&nbsp; &nbsp; &nbsp; &nbsp;as: 'programsclient'&nbsp; &nbsp; &nbsp; &nbsp;attributes: [],&nbsp; &nbsp; &nbsp; &nbsp;require: true,&nbsp;&nbsp; &nbsp; &nbsp; &nbsp;where: { clientId: 1 }&nbsp; &nbsp;}],&nbsp; &nbsp;limit: 0,&nbsp; &nbsp;offset: 10,}
打开App,查看更多内容
随时随地看视频慕课网APP

相关分类

JavaScript