Bind multiple columns in an array/struct with a single table

Question

I would like to use an orm. I looked at here to find some orms with documentations. The problem I'm facing is that none of those orms seems to be able to bind multiple columns into an array or a sub-structure.

To be clear, here is an example. I have a table that looks like that :

Table User
----------
UserId
UserName
UserPositionX
UserPositionY
UserPositionZ

Ideally, I would like to bind the fields into this structure (The array is to be coherent with another API that I'm using client-side) :

type User struct {
    Id int64
    Name string
    Position [3]float64
}

Where UserPositionX binds to Position[0], UserPositionY binds to Position[1] and UserPositionZ binds to Position[2].

I'm open to use a structure like this one instead of an array :

type Vector3 struct {
    X, Y, Z float64
}

type User struct {
    Id int64
    Name string
    Position Vector3
}

But then I'm facing two problems :

I don't want to separate the Position fields in another table. All orms that I looked seems to consider that 1 struct = 1 table.
This structure have to be converted (marshalled) into json. In this json, I need the position to be an array (example : {"id": 42, name: "Foo", position: [6.3, 8.6, 2.65]}).

So my question is : Is there an available orm for go (and sqlite) that is capable of binding multiple columns in an array or struct without creating another table ? If it's not possible with an array, is there a way to customize how a specific type is marshalled into json ?

EDIT

I found a way to change how a specific type is converted into json. I just need to add two methods to the given type. In this example :

func (p *Vector3) MarshalJSON() ([]byte, error) {
    return []byte(fmt.Sprintf("[%v,%v,%v]", p.X, p.Y, p.Z)), nil
}

func (p *Vector3) UnmarshalJSON(data []byte) (err error) {
    _, err = fmt.Sscanf(string(data), "[%v,%v,%v]", &p.X, &p.Y, &p.Z)
    return
}

Result :

test, _ := json.Marshal(User{3, "fooBarBaz", Vector3{42.0, 35, 23.45}})
fmt.Println(string(test))
// ==> {"Id":3,"Name":"fooBarBaz","Position":[42.0,35,23.45]}

test2 := User{}
json.Unmarshal([]byte(`{"Id":3,"Name":"fooBarBaz","Position":[42.0,35,23.45]}`), &test2)
log.Println(test2)
// ==> {3 fooBarBaz {42.0 35 23.45}}

My initial problem still exists because I tested some orms in the list and the only thing I found is by embedding structs, and I need the Vector3 to be an attribute.

It is btw. completely okay to answer your own questions on SO, in case you didn't knew this. — TheHippo, Nov 09 '14 at 17:45
@TheHippo I know it, but my edit doesn't answer to the question. — Sebastien C., Nov 09 '14 at 20:50

score 0 · Answer 1 · edited May 23 '17 at 12:05

in this case you need to combine to features of the database/sql package and the sql database:

You need to group your position columns with GROUP_CONCAT to a comma separated list.
You need to implement the sql.Scanner interface on your type to map the comma separated list to an array.

For the first step you can look at existing questions.

For the second step you could use something like:

type Foo struct {
    Bar []string
}

func (f *Foo) Scan(src interface{}) error {
    switch t := src.(type) {
    // t would then equal "value1, value2, value3"
    case string:
        *f = strings.Split(t, ",")

        return nil
    }

    return ErrBadScanType
}

Example where mapping key1:value1, key2:value2, ... to an object.

PS: You should also consider using some special geo data type for storing vectors if supported by your database.

Thanks for your answer, but this way is not really optimized. — Sebastien C., Dec 17 '14 at 22:32

Bind multiple columns in an array/struct with a single table

EDIT

1 Answers1