Drizzle MySQL Many to Many relations
I am trying to define a many to many relation ship between the employees and strengths tables.
I am getting the error on the employeeStrengthRelations definition. Type 'any[]' is not assignable to type 'Record<string, Relation<any>>'. Index signature for type 'string' is missing in type 'any[]'.ts(2322) Any insight would be greatly appreciated. I want to query employees and get their strengths (title, not the id) in the query. Here are the table definitions. export const employees = mysqlTable('employees', { id: serial('id').notNull().primaryKey(), ..... }); export const strengths = mysqlTable('strengths', { id: serial('id').notNull().primaryKey(), title: varchar('title', { length: 255 }), }); export const employee_strength = mysqlTable('employee_strength', { id: serial('id').notNull().primaryKey(), employee_id: bigint('employee_id', { mode: 'number' }).references(() => employees.id), strength_id: bigint('strength_id', { mode: 'number' }).references(() => strengths.id), order: int('order'), }); export const employeeRelations = relations(employees, ({ many }) => ({ employeesToStrengths: many(employee_strength), })); export const strengthsRelations = relations(strengths, ({ many }) => ({ employeesToStrengths: many(employee_strength), })); This definition is the one giving the error export const employeeStrengthRelations = relations(employee_strength, ({ one }) => ([ strength: one(strengths, { fields: [employee_strength.strength_id], references: [strengths.id], }), employee: one(employees, { fields: [employee_strength.employee_id], references: [employees.id], }), ])); Here is the query, in which I want to get the employees with an array of their strength.titles in order using the order on employee_strength table. const results = await this.db.query.employees.findMany({ with: { employeesToStrengths: true, }, }); Thanks in advance for any insight.
I am getting the error on the employeeStrengthRelations definition. Type 'any[]' is not assignable to type 'Record<string, Relation<any>>'. Index signature for type 'string' is missing in type 'any[]'.ts(2322) Any insight would be greatly appreciated. I want to query employees and get their strengths (title, not the id) in the query. Here are the table definitions. export const employees = mysqlTable('employees', { id: serial('id').notNull().primaryKey(), ..... }); export const strengths = mysqlTable('strengths', { id: serial('id').notNull().primaryKey(), title: varchar('title', { length: 255 }), }); export const employee_strength = mysqlTable('employee_strength', { id: serial('id').notNull().primaryKey(), employee_id: bigint('employee_id', { mode: 'number' }).references(() => employees.id), strength_id: bigint('strength_id', { mode: 'number' }).references(() => strengths.id), order: int('order'), }); export const employeeRelations = relations(employees, ({ many }) => ({ employeesToStrengths: many(employee_strength), })); export const strengthsRelations = relations(strengths, ({ many }) => ({ employeesToStrengths: many(employee_strength), })); This definition is the one giving the error export const employeeStrengthRelations = relations(employee_strength, ({ one }) => ([ strength: one(strengths, { fields: [employee_strength.strength_id], references: [strengths.id], }), employee: one(employees, { fields: [employee_strength.employee_id], references: [employees.id], }), ])); Here is the query, in which I want to get the employees with an array of their strength.titles in order using the order on employee_strength table. const results = await this.db.query.employees.findMany({ with: { employeesToStrengths: true, }, }); Thanks in advance for any insight.
16 Replies
The second parameter of relations doesn't return an array, it should return an object
Easy mistake to make
Thanks that worked .... now getting an error on the query ...
[Nest] 62872 - 12/21/2023, 6:42:27 AM ERROR [ExceptionsHandler] Cannot read properties of undefined (reading 'referencedTable')
TypeError: Cannot read properties of undefined (reading 'referencedTable')
What would the query need to be in order to get the strengths?
const results = await this.db.query.employees.findMany({
with: {
employeesToStrengths: true,
},
});
if i try to use strenghts: true ... i get an error ...
thanks!
Trying to get employee info with an array of strengths( the titles) ...
In employeeRelations, i only have employeesToStrengths and not sure if i need to add strengths in order to get the info i need...
Are you passing All the relations to te drizzle function?
Not sure I follow what you are asking ...
There is somebody with the exact same issue, check here https://discord.com/channels/1043890932593987624/1043890932593987627/1187433002855301222
Yes, they are all in the schema object ...
In that case I could help you investigate if you put together a reproduction repo
ok, I will try to do that ...
any suggestions on how to do that?
Creating a reproduction repo?
yes..
Is your project private? You can just invite me and I'll take a look at your code
right now it is local... i will get it up to a repo and invite you. Thank you for your assistance!!
just added you. Appreciate your help
Where is the drizzle function?
I'm not a nest user so I have no idea what's going on in there
What I can tell you is that whenever this type of error happens, it's because the user forgot to pass the complete schema
Yeah, here it is:
You are importing the schema from employees.entity, which only has the employee table, no relations and no other tables
In employee.service.ts. the entire schema is imported ...
import * as schema from '../drizzle/schema';
That was it in the app module - thanks..
how come i dont get the strengths which is the other side of the many to many ...
I now get the employee_strength which is the junction table...
I am trying to get the "title" on the strengths table ...
thought somehow it would pick up the export const employeeStrengthRelations
When I query against the junction table, I cn get it to provide the employee and strength... just not the way i was expecting...
I get a juntion table row, then an employee object and a strength object...
was hoping to get one employee object then an array of strength objects
So, it's working now?