Problem with findMany with many-to-many relations

First, here's my schema file:


import { integer, primaryKey, sqliteTable, text } from 'drizzle-orm/sqlite-core'
import { relations } from 'drizzle-orm'

export const Entries = sqliteTable('Entries', {
  Id: integer('Id', { mode: 'number' }).primaryKey({ autoIncrement: true }),
  PersonId: integer('PersonId', { mode: 'number' }).notNull(),
  Content: text('Content').notNull(),
  Timestamp: text('Timestamp').notNull(),
})

export const EntriesRelations = relations(Entries, ({ many }) => ({
  EntriesTags: many(EntriesTags),
}))

export const Tags = sqliteTable('Tags', {
  Id: integer('Id', { mode: 'number' }).primaryKey({ autoIncrement: true }),
  Tag: text('Tag').notNull(),
})

export const TagsRelations = relations(Tags, ({ many }) => ({
  EntriesTags: many(EntriesTags),
}))

export const EntriesTags = sqliteTable(
  'EntriesTags',
  {
    EntryId: integer('EntryId', { mode: 'number' })
      .notNull()
      .references(() => Entries.Id),
    TagId: integer('TagId', { mode: 'number' })
      .notNull()
      .references(() => Tags.Id),
  },
  (t) => ({
    pk: primaryKey({ columns: [t.EntryId, t.TagId] }),
  })
)

export const EntriesTagsRelations = relations(
  EntriesTags,
  ({ one }) => ({
    Entry: one(Entries, {
      fields: [EntriesTags.EntryId],
      references: [Entries.Id],
    }),
    Tag: one(Tags, {
      fields: [EntriesTags.TagId],
      references: [Tags.Id],
    }),
  })
)

3 Replies

chris_stOP•3mo ago

Second, here's how I'm querying it:


export const getEntriesForPerson = async (
  context: LocalContext,
  personId: number
): Promise<EntryList | string> => {
    try {
      const entriesFound = await getEntriesDb(
        context
      ).query.Entries.findMany({
        with: {
          EntriesTags: true,
        },
        where: eq(schema.Entries.PersonId, personId),
        limit: 20,
        orderBy: [desc(schema.Entries.Timestamp)],
      })
    
      console.log(

entriesFound is ${JSON.stringify(entriesFound, null, 2)}

)
    
      return ok({ entries: entriesFound })
    } catch (error: any) {
      logTapeLogger.error(

getEntriesForPerson got error ${error.toString()}

)
      return err(error.toString())
    }
}

Finally, the output: entriesFound is [ { "Id": 2, "PersonId": 1, "Content": "Another entry with a tagmarker in it.", "Timestamp": "2024-10-19T19:30:11.173Z", "EntriesTags": [ { "EntryId": 2, "TagId": 1 } ] }, { "Id": 1, "PersonId": 1, "Content": "This is a new entry with tags in it.", "Timestamp": "2024-10-19T19:30:03.568Z", "EntriesTags": [ { "EntryId": 1, "TagId": 1 }, { "EntryId": 1, "TagId": 2 }, { "EntryId": 1, "TagId": 4 } ] } ] Which is really not what I want at all. I want the content of the tags, not the EntryId/TagId pairs. I'm following the https://orm.drizzle.team/docs/rqb#many-to-many many-to-many documentation as closely as I can; I think the with part should be:


with: {
  Tags: true,
},

But that fails with an error:


ERR main getEntriesForPerson got error TypeError: Cannot read properties of undefined (reading 'referencedTable')

Any help very much appreciated!

Anas Badran•6d ago

did you figure it out? I made something like this, but I think there is a better solution..

  with: {
      tags: {
        with: {
          tag: true,
        },
      },
    },

  with: {
      tags: {
        with: {
          tag: true,
        },
      },
    },

chris_stOP•6d ago

Nope, just did a big wad of SQL.

Gaming

Programming

Problem with findMany with many-to-many relations