DT
Drizzle Team•3d 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
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],
  }),
}));
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
        ),
})
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
0 Replies
No replies yetBe the first to reply to this messageJoin

Did you find this page helpful?