Scan results from join

[lexdevelop]

Hello, I'm trying to populate struct which contain one to one relation, but I'm getting the following error duplicate columns: scany: rows contain a duplicate column 'id'" Any help on what I'm doing wrong will be great.

Here are my Entities

type Details struct {
    Id        int       `db:"id"`
    FirstName string    `db:"first_name"`
    LastName  string    `db:"last_name"`
    CreatedAt time.Time `db:"created_at"`
    UpdatedAt time.Time `db:"updated_at"`
}

type User struct {
    Id          int       `db:"id"`
    PublicId    string    `db:"public_id"`
    Email       string    `db:"email"`
    IsActive    bool      `db:"is_active"`
    UserDetails Details   `db:"user_detail"`
    CreatedAt   time.Time `db:"created_at"`
    UpdatedAt   time.Time `db:"updated_at"`
}

And here is the repository function where I'm doing query

func (r *Repository) GetUserByEmail(email string) (*User, error) {
    ctx := context.Background()
    var user User
    err := pgxscan.Get(
        ctx,
        r.dbPoll,
        &user,
        "SELECT * FROM users INNER JOIN users_details ON users_details.id = users.user_detail_id WHERE users.email = @email",
        pgx.NamedArgs{"email": email},
    )
    if err != nil && !errors.Is(err, pgx.ErrNoRows) {
        r.logger.Error("Unable to get user by email", "email", email, "error", err)
        return nil, err
    }

    return &user, nil
}

My database postgress database structure is following

CREATE TABLE users_details
(
    id         SERIAL PRIMARY KEY,
    first_name VARCHAR(100),
    last_name  VARCHAR(100),
    created_at TIMESTAMP DEFAULT NOW(),
    updated_at TIMESTAMP DEFAULT NOW()
);

CREATE TABLE users
(
    id             SERIAL PRIMARY KEY,
    public_id      VARCHAR(36),
    email          VARCHAR(320),
    is_active      BOOLEAN,
    user_detail_id INT UNIQUE,
    created_at     TIMESTAMP DEFAULT NOW(),
    updated_at     TIMESTAMP DEFAULT NOW(),
    CONSTRAINT fk_user_detail FOREIGN KEY (user_detail_id) REFERENCES users_details (id)
);
CREATE INDEX idx_public_id ON users (public_id);
CREATE INDEX idx_email ON users (email);

[georgysavva]

Hi, you need to prefix the columns coming from the user_details table in your SQL:

        "SELECT u.*, ud.id as "user_detail.id", ud.first_name as "user_detail.first_name", ...  FROM users as u INNER JOIN users_details as ud ON users_details.id = users.user_detail_id WHERE users.email = @email",

You need to do this because when you do select * from multiple tables, the database puts all columns in a single namespace with no prefixes. For Scany to be able to map nested structs (your Details struct is nested into the User struct), it needs all columns to be uniquely named, which is achieved by prefixing nested structs.

I hope this helps; feel free to reopen if you have more questions.