Seeking help troubleshooting a JOIN / nested struct problem

[marcesher]

Hi there,

I'm following along with the instructions at https://pkg.go.dev/github.com/georgysavva/scany/dbscan#hdr-Reusing_structs for JOINs / nested structs, and I'm struggling. I'm not getting any errors... I'm just not getting any columns mapped at all.

I have confirmed that with a single table query for each type, they do get mapped properly... it's when trying to join them together that it does not work.

Here are my types:

type IntegrationType struct {
    Id          int    `db:"id"`
    Name        string `db:"integration_type_name"`
    Description string `db:"description"`
}

type Integration struct {
    IntegrationType IntegrationType `db:"rit"`
    Id              int             `db:"id"`
    Name            string          `db:"integration_name"`
    LicenseId       int             `db:"license_id"`

}

And here's the query:

select integrations.id as id, integration_name, license_id, 
          rit.id, rit.integration_type_name, rit.description
    from integrations
    join ref_integration_types rit on integrations.integration_type_id = rit.id
    where integrations.id = $1

I'd appreciate any help. Thanks!

[georgysavva]

Hello! I am here to help you) First of all, when you nest a struct like that:

type Integration struct {
        ...
    IntegrationType IntegrationType `db:"rit"`
        ...
}

The columns that come from the database must have "rit." prefix, like that: "rit.id", "rit.integration_type_name", "rit.description".

In your current SQL query, they miss the "rit." prefix. I recommend you explicitly assign an alias to all columns that you select as you already did for integrations.id as id

If that advice doesn't help, provide the updated SQL, structs definition, and the call to scany library, please.

[marcesher]

Hi @georgysavva thanks so much for the quick reply.

I've confirmed the query has the column prefixes, but I'm getting errors still.

Here's what I have:

i := model.Integration{}
rows, err := dao.Pool.Query(context.Background(),
        `SELECT integrations.id, integration_name, license_id
        rit.id, rit.integration_type_name, rit.description
        FROM integrations
        JOIN ref_integration_types rit ON integrations.integration_type_id = rit.id
        WHERE integrations.id = $1`, id)
err = pgxscan.ScanOne(&i, rows)

Types are unchanged:

type Integration struct {
    IntegrationType IntegrationType `db:"rit"`
    Id              int             `db:"id"`
    Name            string          `db:"integration_name"`
    LicenseId       int             `db:"license_id"`
}

type IntegrationType struct {
    Id          int    `db:"id"`
    Name        string `db:"integration_type_name"`
    Description string `db:"description"`
}

With those types and that query, I get this error:

scany: rows contain a duplicate column 'id'

If, in the query, I remove the rit.id field, I then get this error:

column: 'integration_type_name': no corresponding field found, or it's unexported in model.Integration

So it's as if it's not recognizing the nested type.

Thanks again for any guidance!

[georgysavva]

scany: rows contain a duplicate column 'id' you get this error because integration.id and rid.id result in the same column id in the select statement. when you select columns like that rit.id, the database doesn't add the "rit." prefix to the result column name, you need to do it manually.

The second error column: 'integration_type_name': no corresponding field found, or it's unexported in model.Integration you get because, again, rit.integration_type_name in your select statement has the column name "integration_type_name", but scany is looking for "rit.integration_type_name".

To fix all of those problems you need to give a custom names to columns that you select from the rit table:

SELECT integrations.id, integration_name, license_id
        rit.id as "rit.id", rit.integration_type_name as "rit.integration_type_name", rit.description as "rit.description"
        FROM integrations
        JOIN ref_integration_types rit ON integrations.integration_type_id = rit.id
        WHERE integrations.id = $1

[marcesher]

ah, that worked. thanks!

[pgbytes]

Hey, I am running into a similar problem, but I want to avoid manually defining prefix alias names for each column in the join table. I was trying to use the notate in my query to ask the DB to add the prefix.. so with a query something like:

SELECT integrations.*,
               0 as "notate:rit",
               rit.*
        FROM integrations
        JOIN ref_integration_types rit ON integrations.integration_type_id = rit.id
        WHERE integrations.id = $1 

with this query in the DB, I get the result columns withe prefix, but the scan lib, still complains about the duplicate columns. is there something I am missing?

[georgysavva]

Hi @pgbytes. Thanks for commenting. Could you please create a new issue with more information about your problem:

1. The SQL query (the one you posted already)
2. Go definition of your structs that you try to scan into
3. Your code where you call the scany library
4. List of columns your database returns when you execute the query in a GUI client like DataGrip, TablePlus, or PGAdmin.