golang mysql在结构中存储了任意行

Question

so I have sql that looks like this

SELECT accounts.id, accounts.username, accounts.password, 
      accounts.created, accounts.last_logged_in, accounts.access,
      banned.reason, banned.expires,
      player.x, player.y, player.zone
FROM accounts
LEFT JOIN banned
ON accounts.id = banned.account_Id
INNER JOIN player
ON accounts.id = player.account_Id
WHERE accounts.username = username

If I wanted to store this in a struct in go I would normally do this:

type Account struct {
    Id           int
    Username     string
    Password     string
    Email        string
    Created      time.Time
    LastLoggedIn time.Time
    AccessLevel  int
    Location     struct {
        Zone string
    }
    Banned []*Banned
}

type Banned struct {
    Reason  string
    Expires time.Time
}
reply := new(Account)

stmt, err := this.Database.Prepare(("CALL findUser(?)"))
    defer stmt.Close()

if err != nil {
    logger.ERROR.Println(err)
    return err
}

err = stmt.QueryRow(args).Scan(&reply.Id, &reply.Username ... you get the idea)

however this is not going work because scan is going to expect a value for every argument and we have left joined onto banned! As the user could have 0 - N bans what’s the best way to tackle this?

Many thanks Zidsal

Answer 1

I feel that either your example or question isn't what you really wanted to describe, since they don't really ask for the same problem.

Reading the provided example, you have two different types to scan into (an Account, left join a Banned), that will be repeated for each row of the result. So you just have to create a new Banned struct at the same time that your Account struct and use it to scan the values, then add it to the Account.Banned slice. Loop for each row, and you're done.

Reading your question, I think that your sql query is somewhat wrong: you have multiples accounts that each have several bans, and you would like to have one account by result row with every ban in it. To do that, you will need to tweak your query with a GROUP BY statement to get one row by account, then the smartest way would be to do a GROUP_CONCAT to get every ban into one attribute that you could then parse accordingly. Example (overly simplified to better expose principle):

  SELECT accounts.id, GROUP_CONCAT(banned.id SEPARATOR ',') as bans
  FROM accounts
  LEFT JOIN banned
  ON accounts.id = banned.account_Id
  WHERE accounts.username = username
  GROUP BY accounts.id

You just have to scan the bans column to a string, split it around ,, etc. In your case, the parsing will be more complex, as you need 2 values in one column, but the principle is the same.