[Question] Incremental filling of destination slice/struct

[itaranto]

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?

[go-jet]

Incremental filing at the moment is unspecified. QRM will append to non-empty array, but it will not try to group result set into existing array elements(it will create a new array element). I'm not sure how managed to fill Vulnerabilities or Threats of the existing components. This shouldn't be possible.

[itaranto]

Incremental filing at the moment is unspecified. QRM will append to non-empty array, but it will not try to group result set into existing array elements(it will create a new array element). I'm not sure how managed to fill Vulnerabilities or Threats of the existing components. This shouldn't be possible.

Maybe I've got confused during my testing, you're saying each one of QueryContext calls will append to the array, so effectively having the same component twice, one with vulnerabilities and the other with threats. Is that right?

[go-jet]

Yeah, I suspect so.

[itaranto]

OK, you can close this then.

Unless someone can suggest a way to "preload" relations in a more efficient way, I was using lots of joins for this which doesn't scale at all.

Thank you.