[Question] How to populate nested "entities" that require self-join #252
Description
I have the following models:
type Components struct {
ID uuid.UUID `sql:"primary_key"`
// ...
}
type Vulnerabilities struct {
ID uuid.UUID `sql:"primary_key"`
// ...
}
type ComponentVulnerabilities struct {
ComponentID uuid.UUID `sql:"primary_key"`
VulnerabilityID uuid.UUID `sql:"primary_key"`
// ...
}
type ComponentChildren struct {
ComponentID uuid.UUID `sql:"primary_key"`
ChildID uuid.UUID `sql:"primary_key"`
// ...
}And this is the struct where I want the data to be mapped to:
type component struct {
model.Components
Vulnerabilities []model.Vulnerabilities
Children []component
}Notice the Children field refers to itself : component instead of model.Components because I want children to have the same data as the parent, in this case, the Vulnerabilities.
If I didn't want to populate the children's sub-relations, I could have done something like this:
type component struct {
model.Components
Vulnerabilities []model.Vulnerabilities
Children []model.Components `alias:"children"`
}And then make a query like this:
tableChildren := table.Components.AS("children")
stmt := postgres.SELECT(
table.Components.AllColumns,
table.Vulnerabilities.AllColumns,
tableChildren.AllColumns,
).FROM(
table.Components.
LEFT_JOIN(
table.ComponentVulnerabilities,
table.ComponentVulnerabilities.ComponentID.EQ(table.Components.ID),
).
LEFT_JOIN(
table.Vulnerabilities,
table.Vulnerabilities.ID.EQ(table.ComponentVulnerabilities.VulnerabilityID),
).
LEFT_JOIN(
table.ComponentChildren,
table.ComponentChildren.ComponentID.EQ(table.Components.ID),
).
LEFT_JOIN(
tableChildren,
tableChildren.ID.EQ(table.ComponentChildren.ChildID),
),
)In order to get the children with its sub-entities, I know that I need to do a sub-query but I'm not sure if the QRM supports this.
I tried something like this:
tmpTableChildren := postgres.SELECT(
table.Components.AllColumns,
table.Vulnerabilities.AllColumns,
).FROM(
table.Components.
LEFT_JOIN(
table.ComponentVulnerabilities,
table.ComponentVulnerabilities.ComponentID.EQ(table.Components.ID),
).
LEFT_JOIN(
table.Vulnerabilities,
table.Vulnerabilities.ID.EQ(table.ComponentVulnerabilities.VulnerabilityID),
),
).AsTable("tmp_children")
tmpTableChildrenID := table.Components.ID.From(tmpTable)
stmt := postgres.SELECT(
table.Components.AllColumns,
table.Vulnerabilities.AllColumns,
tmpTableChildren.AllColumns(),
).FROM(
table.Components.
LEFT_JOIN(
table.ComponentVulnerabilities,
table.ComponentVulnerabilities.ComponentID.EQ(table.Components.ID),
).
LEFT_JOIN(
table.Vulnerabilities,
table.Vulnerabilities.ID.EQ(table.ComponentVulnerabilities.VulnerabilityID),
).
LEFT_JOIN(
table.ComponentChildren,
table.ComponentChildren.ComponentID.EQ(table.Components.ID),
).
LEFT_JOIN(
tmpTableChildren,
tmpTableChildrenID.EQ(table.ComponentChildren.ChildID),
),
)But I can't make the mapper to fill the structs in the way I need it.