Sequelize - Query with Many-to-Many relationship using where and limit

user_10473393 0
Viewed 1364

I have this relationship:

Clients -> ProgramsClients <- Programs

What i'm trying to do is basically:

SELECT * FROM Programs p JOIN ProgramsClients pc on p.id = pc.programId WHERE pc.clientId = 1 LIMIT 0, 100;

I have managed to reach something like this with the following code:

query = {
   include: [{
       model: models.Clients,
       attributes: [],
       require: true, 
   }],
   where: { '$Clients.id$': 1 }
}

models.Programs.findAll(query) // This works

Which generates:

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;

This works, but when i try limitting it, i get an error. The code:

query = {
   include: [{
       model: models.Clients,
       attributes: [],
       require: true, 
   }],
   limit: 0,
   offset: 10,
   where: { '$Clients.id$': 1 }
}

models.Programs.findAll(query) // This fails

Which generates:

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`;

Error: DatabaseError [SequelizeDatabaseError]: Unknown column 'Clients.id' in 'where clause'

NOTE: I'm using a MySQL database.

Is there any easier way to solve this and generate the desired (or similar) result for SQL?

Thanks in advance

sequelize.js node.js javascript
1 Answers

I took a pause. And when i returned, i managed to solve it.

Basically, i had misread the super many-to-many section from docs.

You can simply define an One-to-many relationship (even if you're using many-to-many relationships) with the association's table (In this case, ProgramsClients) and then include ProgramsClients and do whatever you want. (You must declare an id column for ProgramsClients for this).

query = {
   include: [{
       model: models.ProgramsClients,
       as: 'programsclient'
       attributes: [],
       require: true, 
       where: { clientId: 1 }
   }],
   limit: 0,
   offset: 10,
}
user_104733930
Related