Open
Description
I'm attempting to optimize the query presented in this issue, this slightly different model that the one presented there:
type component struct {
model.Components
Vulnerabilities []model.Vulnerabilities
Threats []model.Threats
}The idea is simple: instead of doing one LEFT JOIN per each "sub-entitity", I do one query per-sub entity and then I reuse the same destination slice. Very similar to how GORM's Preload works.
This works fine for populating the "parent" entity, but when populating the "sub-entitities", then the last seem to wipe out the previous ones.
Let me explain this in code which will be clearer:
// Select components that match certain filters.
stmt := postgres.SELECT(
table.Components.AllColumns.Except(componentsExcludeColumns),
).FROM(
// Here's a big join with all the many-to-many tables which I want to use for the filters.
).WHERE(
expr,
).GROUP_BY(
table.Components.ID,
).ORDER_BY(
table.Components.ID.DESC(),
).LIMIT(
int64(pageSize),
).OFFSET(
int64((pageNumber - 1) * pageSize),
)
components := []*component{}
if err := stmt.QueryContext(ctx, r.db, &components); err != nil {
return nil, err
}
// Up to this point, `components` is populated correctly.
componentExpr := []postgres.Expression{}
for _, component := range components {
componentExpr = append(componentExpr, postgres.UUID(component.ID))
}
// Now, reuse the same `components` slice to populate the component's vulnerabilities...
stmt = postgres.SELECT(
table.ComponentVulnerabilities.ComponentID.AS("components.id"),
table.Vulnerabilities.AllColumns,
).FROM(
table.ComponentVulnerabilities.
INNER_JOIN(
table.Vulnerabilities,
table.Vulnerabilities.ID.EQ(table.ComponentVulnerabilities.VulnerabilityID),
),
).WHERE(
table.ComponentVulnerabilities.ComponentID.IN(componentExpr...),
)
if err := stmt.QueryContext(ctx, r.db, &components); err != nil {
return nil, err
}
// So far, this works too: The components data from the embedded `Components` field is sill here
// but now with the `Vulnerabilities` as well.
// Now, reuse the same `components` slice to populate the component's threats...
stmt = postgres.SELECT(
table.ComponentThreats.ComponentID.AS("components.id"),
table.Threats.AllColumns,
).FROM(
table.ComponentThreats.
INNER_JOIN(
table.Threats,
table.Threats.ID.EQ(table.ComponentThreats.ThreatID),
),
).WHERE(
table.ComponentThreats.ComponentID.IN(componentExpr...),
)
if err := stmt.QueryContext(ctx, r.db, &components); err != nil {
return nil, err
}
// Here's the problem: This correctly populates the `Threats` but `Vulnerabilities` somehow get
// zeroed out (for components that have both).
// Reversing the order gets me the opposite: I get the `Vulnerabilities` but not the `Threats`.It seems the QRM doesn't support "incremental" filling of a struct or silce, I'm I wrong?
Is there a way to do this?