'Sequelize self-referencing table/model not going recursive
Lets suppose I have the following table in my RDBMS called "family":
| id | ancestor_id | family_member |
|---|---|---|
| 1 | null | great-great-grandfather |
| 2 | 1 | great-grandfather |
| 3 | 2 | grandfather |
| 4 | 3 | father |
| 5 | 4 | son |
| 6 | 5 | grandson |
"id" is the primary key and an auto-increment field, "ancestor_id" is a foreign key witch references it's own table, in other words, "ancestor_id" is a foreign key of "family", target column "id". A table that references itself, right? So far, nothing new. To better illustrate the example, I've added a varchar column, "family_member", it will come in handy latter as one visualizes the intend.
I'm using Sequelize as my ORM solution. I'm not a beginner in it, nor am I a beginner in ORM technology. I was using Hibernate before annotations were a thing, and before JPA started encapsulating it (some 17 years ago).
How should I configure the Model and put my query together, in order for Sequelize to give me the following object, as I query for family member id=6 (the grandson):
{
id: 6,
ancestor_id: 5,
family_member: "grandson"
ancestor: {
id: 5,
ancestor_id: 4,
family_member: "son"
ancestor: {
id: 4,
ancestor_id: 3,
family_member: "father"
ancestor: {
id: 3,
ancestor_id: 2,
family_member: "grandfather"
ancestor: {
id: 2,
ancestor_id: 1,
family_member: "great-grandfather"
ancestor: {
id: 1,
ancestor_id: null,
family_member: "great-great-grandfather"
}
}
}
}
}
}
No matter what I do, Sequelize doesn't go "recursive" on that one, all that I can get is one level deep, if I query for the "grandson" and ask to include the Model itself in the query, it will only return me the "son", and nothing above that.
What I need for this one is for Sequelize to go on until it finds a null "ancestor_id", as you can see in my JSON example.
Thanks for the time and patience.
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
| Solution | Source |
|---|
