<p>I have a struct, User, shown below:</p>
<pre><code> type User struct {
DbID string
DbUsername string
DbPasswordHash string
DbPasswordSalt string
}
</code></pre>
<p>I query my database, like so:</p>
<pre><code> rows, err := db.Query("SELECT id FROM users WHERE username = $1",
sender)
defer rows.Close()
if err != nil {
return err
}
users := make([]*User, 0)
for rows.Next() {
user := new(User)
err := rows.Scan(&user.DbID)
if err != nil {
log.Fatal("boop: ", err)
return err
}
users = append(users, user)
}
</code></pre>
<p>Now I have to parse the value stored in the User struct to insert somewhere else in the database. This is my issue:</p>
<pre><code> // There has to be a better way to do this...
for _, user := range users {
sqlStatement := `INSERT INTO message (creator_id, message_body, create_date)
VALUES ($1, $2, $3)`
_, err = db.Exec(sqlStatement, user.DbID, message, timestamp)
}
</code></pre>
<p>How can I do this in a flat manner? Or just some better way. My issue with this is later down I have to iterate over a User struct and a Message struct, so obviously this does not work </p>
<hr/>**评论：**<br/><br/>Killing_Spark: <pre><p>I have some fundamental questions: you look in your database for id(s) for some username. I mean sure there can be more then one user with the same name, but then you save the message that was sent for every one of these users? Maybe Im not seeing something, but I think that isnt right? </p>
<p>So i'd suggest instead of passing the username, pass the id to your method, then you dont need to query for the id and you dont need the for loop. (you will need to give the users unique ids anyways so you can simply use the dbID) </p>
<p>Correct me please if that is wrong in some way :) </p></pre>ashishduhh1: <pre><p>I assume you're using MySQL. I suggest you bypass memory altogether and just do a <a href="https://dev.mysql.com/doc/refman/5.7/en/insert-select.html" rel="nofollow">INSERT...SELECT statement</a>.</p></pre>Idontlikecold: <pre><p>Postgres but I'll check it out, thanks </p></pre>ashishduhh1: <pre><p><a href="http://www.chesnok.com/daily/2013/11/19/everyday-postgres-insert-with-select/comment-page-1/" rel="nofollow">http://www.chesnok.com/daily/2013/11/19/everyday-postgres-insert-with-select/comment-page-1/</a></p></pre>Idontlikecold: <pre><p>Thank you this looks prefect :) </p></pre>FUZxxl: <pre><p>I wonder why they call it “INSERT ... SELECT,” it's just a normal <code>INSERT</code> statements with a <code>SELECT</code> subclause instead of a <code>VALUES</code> subclause.</p></pre>joushou: <pre><p>Probably because "<code>INSERT</code> ... <code>SELECT</code>" is shorter than "A normal <code>INSERT</code> statement with a <code>SELECT</code> subclause instead of a <code>VALUES</code> subclause".</p></pre>EliAscent: <pre><p>Is it wise to store a hash and salt like that?</p></pre>jerf: <pre><p>The salt must be stored to work, because it has to be fed back to the hash algorithm to check it. Can't do that if you've forgotten what it is.</p>
<p>What's a bad sign is that they are being stored manually separated, when all the good password algorithms already store the salt in their default string serialization. It probably means the hash is md5 or something dangerously unsafe instead of bcrypt or scrypt or something safe.</p></pre>Idontlikecold: <pre><p>Actually they aren't separate, the salt in the struct is old code before I looked at go's hashing func. Everything is squeaky clean don't worry :) </p></pre>EliAscent: <pre><p>Ok great :]</p></pre>jerf: <pre><p>Acknowledged :)</p></pre>kemitche: <pre><p>Based on the later part of your question, it <em>sounds</em> like your main question is "how do I insert multiple messages into the database with a single db.Exec?"</p>
<p>I'd recommend using a tool like <a href="https://godoc.org/github.com/Masterminds/squirrel" rel="nofollow">squirrel</a> to help build the queries. It lets you do something like:</p>
<pre><code>query = sq.Insert("messages").Columns("creator_id", "message_body", "create_date")
for _, user := range users {
query = query.Values(user.DbId, message, time.Now())
}
sql, args, err := query.ToSql()
if err != nil { panic("I build a bad query") }
db.Exec(sql, args...)
</code></pre></pre>
