Drizzle Team•12mo ago

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.

16 Replies

Angelelz•12mo ago

The second parameter of relations doesn't return an array, it should return an object

export const employeeStrengthRelations = relations(employee_strength, ({ one }) => ({ // <--here
  strength: one(strengths, {
    fields: [employee_strength.strength_id],
    references: [strengths.id],
  }),
  employee: one(employees, {
    fields: [employee_strength.employee_id],
    references: [employees.id],
  }),
})); // <-- and here

export const employeeStrengthRelations = relations(employee_strength, ({ one }) => ({ // <--here
  strength: one(strengths, {
    fields: [employee_strength.strength_id],
    references: [strengths.id],
  }),
  employee: one(employees, {
    fields: [employee_strength.employee_id],
    references: [employees.id],
  }),
})); // <-- and here

Easy mistake to make

DonOP•12mo ago

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...

Angelelz•12mo ago

Are you passing All the relations to te drizzle function?

DonOP•12mo ago

Not sure I follow what you are asking ...

Angelelz•12mo ago

There is somebody with the exact same issue, check here https://discord.com/channels/1043890932593987624/1043890932593987627/1187433002855301222

DonOP•12mo ago

Yes, they are all in the schema object ...

Angelelz•12mo ago

In that case I could help you investigate if you put together a reproduction repo

DonOP•12mo ago

ok, I will try to do that ... any suggestions on how to do that?

Angelelz•12mo ago

Creating a reproduction repo?

DonOP•12mo ago

yes..

Angelelz•12mo ago

Is your project private? You can just invite me and I'll take a look at your code

DonOP•12mo ago

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

Angelelz•12mo ago

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:

import * as schema from './drizzle/models/employees.entity';

import * as schema from './drizzle/models/employees.entity';

You are importing the schema from employees.entity, which only has the employee table, no relations and no other tables

DonOP•12mo ago

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

Angelelz•12mo ago

So, it's working now?

DonOP•12mo ago

Yes, working... I was thinking I would be able to get an employee with an array of strenths ... I am querying the employee table... I then tried the employee_strength table, the junction table which pulls in the employee and strength ... at least it has the data, a bit of redundant data.... thanks for your time and assisntance! you were a great help, thank you!

Gaming

Programming

Drizzle MySQL Many to Many relations