Joining mySQL tables in Go with some unknown columns

Question

I've been searching high & low for something that'd work for Joining two tables with some unknown columns.

I've tried this:

sqlx.Queryx with

columns, err := rows.Columns()

and: Handling Dynamic Queries (cant scan into struct) and How to read a row from a table to a map, Without knowing columns

but columns doesn't preserve the prefix of my mysql table join name.

I do have Structs for the known columns from each table (I know 90% of the columns, but we allow users to add their own columns to certain mySQL tables and then we have to present those back. We do know what datatype those will be generally github.com/jmoiron/sqlx

Something like the following gets me most of the way there, most of the time:

d := make(map[string]interface{})

    sqlstr := `select *  from quotes me join quote_lines ql on ql.quote_id = me.id where me.id = 11 limit 3`
    q, err := db.Queryx(sqlstr)
    if err != nil {
        fmt.Println("error is ", err)
    }
    defer q.Close()

    row := db.QueryRowx(sqlstr)

    var columns []*sql.ColumnType
    coltype := map[string]interface{}{}

    columns, err = row.ColumnTypes()
    if err != nil {
    }
    for _, h := range columns {
        coltype[h.Name()] = h.DatabaseTypeName()
    }

    res := []*map[string]interface{}{}
    for q.Next() {
        err = q.MapScan(d)
        if err != nil {
            fmt.Println("error is ", err)
        }

        for i, h := range d {
            if h != nil {
                switch h.(type) {
                case []byte:
                    if coltype[i] == "DECIMAL" || coltype[i] == "NUMERIC" {
                        d[i] = MySQLDecimalToFloat(h)
                    } else {
                        d[i] = string(h.([]byte))
                    }
                default:
                    if coltype[i] == "TINYINT" {
                        b, err := strconv.ParseBool(strconv.FormatInt(h.(int64), 10))
                        if err != nil {
                            log.Log.Infoln("tinyint bool err: ", err)
                            d[i] = false
                        } else {
                            d[i] = b
                        }
                    }
                }
            }
        }
        res = append(res, &d)

    }

Unfortunately, this squashes dupe column names between my tables (so long me.id, you now have the value of ql.id saved in key "id").

Is there a way to perma-fix prefixes to known & unknown columns? Or, to do a structScan for all the known columns (preserving the join name prefix?) Or, is this completely an XY problem?

If I know all of the columns, I could build a new Struct:

type Quote struct {
   Id uint `json:"id"`
   Name string `json:"name"`
}
type QuoteLine struct {
   Id uint `json:"id"`
   Qty float64 `json:"qty"`
   Quote_id uint `json:"quote_id"`
}
type New struct {
   Quote Quote `db:"me"`
   QL QuoteLine`db:"ql"`
// I tried adding in a []map[string]interface{} here & doing a mapscan, also no dice
}

However, rows.StructScan isn't picking up the prefixes (for either one).

What do people do for additional/unknown columns when joining tables?

I could, in theory select * single row from each table so that I know all of the additional columns that I have access to, and then prefix them with the table join name "ql_mybonuscolumn" and "me_mybonuscolumn". However, while that would take care of my unknown columns, it wouldn't be an easy fix for the known columns. (I suppose I could prefix the table join name for each column, then modify my structs to include the prefix, then use structScan, but then the unknown columns wouldn't transfer. It appears I'll have to use MapScan for those to work.

Is there a way to keep them separate in the scan back?

I would really like my response to be able to be something like this (after running JSONMarshal):

[{
  me: {
   id: 1,
   name: "first Quote for Customer",
  },
  ql: {
   id: 5,
   quote_id: 1,
   qty: 34.32
  }
}]

Any hints or direction would be extremely welcome.

I haven't looked into SliceScan, but I'm not sure how that would help if I want my key/values known afterwards

If I can't find anything that would do this, maybe I'll just pivot to declaring it all as a json response & forward it to the frontend using this question: (but I'd really like to not do that, as some of the known columns will require business logic to be applied): How to convert result table to JSON array in MySQL

Joining mySQL tables in Go with some unknown columns

0 Answers0