Drizzle Team•4mo ago

Relations and inheritance mapping

Hello! I have a schema where a question can be polymorphic - that is there's a generalized (base) Question that can be specialized: TrueFalseQuestion, MultipleChoiceQuestion etc. Specialized types have their own attributes, that are unique to them (such as is_correct for true/false). I would like to know if there's a way to use Drizzle Query and don't nest the associated tables, but instead return the attributes within the object itself:

// Instead of this
{
  "id": "1b22a63f-d935-44c2-ae52-38b899788e64",
  "type": "true_false",
  "question": "Does drizzle support polymorphic associations?",
  "answers": [],
  // nested relation, queried through findMany's `with`
  "trueFalseQuestion": {
    "isCorrect": true
  }
}

// Return this
{
  "id": "1b22a63f-d935-44c2-ae52-38b899788e64",
  "type": "true_false",
  "question": "Does drizzle support polymorphic associations?",
  "answers": [],
  "isCorrect": true
}

// Instead of this
{
  "id": "1b22a63f-d935-44c2-ae52-38b899788e64",
  "type": "true_false",
  "question": "Does drizzle support polymorphic associations?",
  "answers": [],
  // nested relation, queried through findMany's `with`
  "trueFalseQuestion": {
    "isCorrect": true
  }
}

// Return this
{
  "id": "1b22a63f-d935-44c2-ae52-38b899788e64",
  "type": "true_false",
  "question": "Does drizzle support polymorphic associations?",
  "answers": [],
  "isCorrect": true
}

Mapping it manually ofc isn't a problem. I'm just curious whethe it's possible to do it through drizzle directly? Currently I query the data as follows:

database.query.questions.findMany({
  with: {
    answers: true,
    trueFalseQuestion: {
      columns: {
        isCorrect: true,
      },
    },
  },
  where: (questions, { eq }) => eq(questions.quizId, quizId),
});

database.query.questions.findMany({
  with: {
    answers: true,
    trueFalseQuestion: {
      columns: {
        isCorrect: true,
      },
    },
  },
  where: (questions, { eq }) => eq(questions.quizId, quizId),
});

1 Reply

KubaOP•4mo ago

I was thinking something along the lines of how the Hibernate or SQLAlchemy does the "inheritance mappings" with join table strategy https://docs.sqlalchemy.org/en/20/orm/inheritance.html

Gaming

Programming

Relations and inheritance mapping