Supporting nested data structures

[RHavar]

Often it's quite useful to have nested data-structures, because a plain join would lead to many conflicts and confusions. Imagine this simple (postgres) schema:

CREATE TABLE users (
  id         bigserial   PRIMARY KEY,
  uname      text        NOT NULL,
  created    timestamptz NOT NULL DEFAULT NOW()
);

CREATE TABLE chat_messages (
  id         bigserial primary key,
  message    text      NOT NULL,
  user_id    bigint    NOT NULL REFERENCES users(id),
  created timestamptz NOT NULL DEFAULT NOW()
);

and we model this with go structs:

type ChatMessage struct  {
    Id int64
    Message string
    UserId int `db:"user_id"`
    Created time.Time
}

type User struct {
  Id          int64
  Uname       string
  Created     time.Time
}

Now let's say we want to query the last chat messages, perhaps the most obvious way would be:

SELECT
   chat_messages.*
   users.*
FROM chat_messages
JOIN users ON users.id = chat_messages.user_id
ORDER BY chat_messages.id DESC LIMIT 100;

But reading this into the structure:

type UserAndMessage struct {
  User
  ChatMessage
}

actually leads to silent corruption! (most likely due to the duplicate column names). Probably the correct behavior here would be to give an error, or assume that the duplicate fields would be in the order of "User" then "ChatMessage"

But ignoring that, trying to work around this and perhaps the cleanest way to write the query in the first place would actually be:

SELECT
  chat_messages.* as message
  ROW(users.*) AS user,
FROM chat_messages
JOIN users ON users.id = chat_messages.user_id
  ORDER BY chat_messages.id DESC LIMIT 100;

which you would expect to be able to read into the struct

type MessageWithUser struct {
    models.ChatMessage
    User models.Users
}

However this gives an error? Perhaps this is something that should be supported?

[robert-zaremba]

I have similar error when doing "double nesting":

type Base struct {
    X int
}
type A1 struct {
    Base
}
type A2 struct {
    Base
    A int
}

[Alex1sz]

+1

[huyujie]

+1

[jawadcode]

This seems like a pretty important thing, is it already implemented or has it just been ignored?

[j3ch]

I'm having this exact problem. The fact that it corrupts data for the duplicated fields silently is very concerning.

Is there an update on this issue? or a reasonable workaround?

[ntbosscher]

I believe you can get around this issue by modifying the output column names on your query.

SELECT
   chat_messages.id "chat.id", --... other chat_messages columns
   users.id "user.id", -- ... other user columns
FROM chat_messages
JOIN users ON users.id = chat_messages.user_id
ORDER BY chat_messages.id DESC LIMIT 100;

This will output something like this

chat.id	user.id
1222	123

And sqlx will basically do

userMessage := UserMessage{}
userMessage.Chat.ID = 1222
userMessage.User.ID = 123

The issue with your original query is that the output column names aren't fully qualified, so you get something like this

id	id
1222	123

(sometimes your ide will auto-prefix the column names for you, try running your query in command line and see what you get for column names)

And sqlx will basically do

userMessage := UserMessage{}
userMessage.ID = 1222
userMessage.ID = 123

The shadowing is a golang "way of doing things" that you can't really get around.

Hope that helps

[arvenil]

@ntbosscher This is great, but this behavior doesn't seem to be documented? I was trying to figure out where this is done in the code but I failed. How did you find out about this?

[ntbosscher]

@Alex1sz reading docs and source code :)