Drizzle TeamDT
Drizzle Team•9mo ago
Meexa

Need help with relation

I have two tables: characters and character_scores. They are linked by an 
id
 column. characters can have many of the same ID and character_scores as well. I want to make a relation between those ID's so that when I query a unique row from characters I want to include the associated character_scores row(s). So for example (pseudo sql): 

from characters I want 1 row where id = x and specialization = y and include columns from character_scores where id = x and category = z


I hope that makes sense.

Currently I have this:
export const charactersRelations = relations(characters, ({ many }) => ({
  characterScores: many(characterScores),
}));

export const characterScoresRelations = relations(characterScores, ({ one }) => ({
  character: one(characters, {
    fields: [characterScores.characterId],
    references: [characters.id],
  }),
}));


But when I write a query like this, I can't select the columns from character_scores
db.query.characters.findMany({
      with: { characterScores: true },
      columns: {
        specialization: true,
        score: true // ! type error, score does not exist
      },
      where: (columns, { and, eq, gt }) =>
        and(
          eq(columns.specialization, specname),
          gt(columns.category, 'foo'), // ! type error, category does not exist
        ),
})


Maybe I'm doing something wrong, not sure.

Edit: The return type does include character_scores so that part is working
Was this page helpful?