左联接如何与sqlx一起使用

Question

I'm trying to inner join two tables person and profile with a simple query which seems to work fine with mysql but not with sqlx. Here's my go code:

package main 

import (
    "fmt"
    "github.com/jmoiron/sqlx"
    _ "github.com/go-sql-driver/mysql"
)

type Person struct {
    Id      int64   `db:"id"`
    Name    string  `db:"name"`
    Email   string  `db:"email"`
}

type Profile struct {
    Id          int64   `db:"id"`
    Face        string  `db:"face"`
    Hair        string  `db:"hair"`
    Person
}

func main() {
    DB, err := sqlx.Connect("mysql", "root:hackinitiator@/dusk")
    if err == nil {
        fmt.Println("sucess!!")
    } 
    var q []Profile
    DB.Select(&q, "select person.id, person.name, person.email, profile.id, profile.face, profile.hair from profile left join person on person.id = profile.person_id")
    fmt.Println(q)
}

The mysql query produces the following output:

+------+------+---------+----+----------+--------+
| id   | name | email   | id | face     | hair   |
+------+------+---------+----+----------+--------+
|    1 | yoda | nomail  |  1 | round    | brown  |
|    5 | han  | nomail1 |  3 | circle   | red    |
|    6 | yun  | nomail2 |  4 | triangle | yellow |
|    7 | chi  | nomail3 |  5 | square   | green  |
+------+------+---------+----+----------+--------+

which is perfectly fine but my go program is not responding as expected. The struct is unable to capture the profile id(empty in output) and person id is replaced with profile id. Below is the output(formatted):

[
{0 round brown {1 yoda nomail}} 
{0 circle red {3 han nomail1}} 
{0 triangle yellow {4 yun nomail2}} 
{0 square green {5 chi nomail3}}
]

I'm unable to figure out what went wrong.

Answer 1

You need to change db name in person struct like below i'm describing because there would be two column with same name i.e. id so it scan only last one id which is in your profile table and not scan of person table, so follow the struct as mentioned below.

type Person struct {
    Id      int64   `db:"pId"`
    Name    string  `db:"name"`
    Email   string  `db:"email"`
}

And then write your query with as for person.id like

DB.Select(&q, "select (person.id) as pId, person.name, person.email, profile.id, profile.face, profile.hair from profile left join person on person.id = profile.person_id")