5

My question in a nutshell: can I use sqlx's StructScan to populate two embedded structs with values sourced from the same SQL table joined twice?

The help files to the useful sqlx package state this:

A StructScan will set an id column result in Person.AutoIncr.ID, also accessible as Person.ID. To avoid confusion, it's suggested that you use AS to create column aliases in your SQL instead.

Supposed I have this SQL query (parent-child, people to phones):

func getSQL() string {
    return `SELECT * 
        FROM person
        LEFT JOIN phones AS Phone1 ON Phone1.phone_id = person_phoneID1
        LEFT JOIN phones AS Phone2 ON Phone2.phone_id = person_phoneID2
        WHERE people_id = 1;`
}

Using sqlx and StructScan, I'd like to populate a struct full of embedded structs, something like this:

//Struct with embedded structs
type personHelper struct{
    Person
    Phone1 //Should I use the same name as SQL table alias?
    Phone2
}

type Phone1 struct {
    Phone //Underlying struct
}

type Phone2 struct{
    Phone
}

//Base structs, with tags to match up fields
type Person struct{
     ID `db:"person_id"`
     Name `db:"person_name"`
     Phone1 `db:"person_phoneID1"`
     Phone2 `db:"person_phoneID2"`
}

type Phone struct{
     ID int64 `db:"phone_id"`
     Number string `db:"phone_no"`
     //etc.
}

I might have a function something like this:

func getPeople(){

    parseRows := func(rows *sqlx.Rows) {
        for rows.Next() {
            var ph personHelper
            err := rows.StructScan(&ph)
            if err != nil{
                //etc.
            }
        }
    } 

    sql := getSQL()

    sqlutils.GetRows(parseRows, sql)//GetRows executes the SQL query and returns rows for processing     
}

I can populate one phone number, but not both. I'm not sure whether I'm understanding the aliasing instructions correctly.

I'd appreciate any insights.

Thanks.

Brent
  • 805
  • 9
  • 20
  • Why are you joining twice? – Jonathan Hall Jan 10 '18 at 07:58
  • I'm not understanding the goal here. This feels a bit like an XY problem; focusing more on a particular solution than the actual goal. Can you explain your objective more clearly? – Jonathan Hall Jan 10 '18 at 08:00
  • Sure: the objective is to use a single SQL statement to create and populate a set of structs with parent-child relationships. The example is contrived, but suppose you store customers in one table, and representatives in another. Both of these tables represent people, and both have phone numbers, stored in a third table. If you want to get all customers for a given representative, and also phone numbers for both, you'd need to join the phone numbers table twice. – Brent Jan 10 '18 at 20:20
  • In general, if you want to populate/update multiple tables, you need multiple statements. Or you can use a stored procedure, to make them appear as a single statement to the client. Some RDBMSs will allow indirect updates of multiple tables, with triggers or similar functionality. But that's entirely a function of your RDBMS, and doesn't relate to Go or sqlx at all. – Jonathan Hall Jan 11 '18 at 07:43

0 Answers0