query tables and conditions not cleared between different calls

[dingquan]

Your Question

I'm upgrading from gorm v1 to v2 and is totally confused about how I'm supposed to start a new query fresh. In v1, I can simply do db.Where("id=?", id).First(&user), which would result in a query like select * from users where id = ? no matter how many times it's called. With v2, that stopped working, seems like every time it's called, the condition is appended to the condition from last call. So if I call the query with id = 10 on the first time and id = 20 on the second time. The sql query would become select * from users where id = 10 and id = 20 and it will return no rows as a result. The method chaining doc seems to have warned about this behavior. But the suggested Session(&gorm.Session{}) doesn't seem to work all the time.

If I call db.Create(user), then db.Save(project), the second call will create sql to insert into the users table instead of projects table! That's totally unexpected . It appears Session(&gorm.Session{NewDB:true} seems to solve the problem. But the documentation says it will start a new db connection. So it won't work if I want multiple queries to be in one transaction?

The document you expected this should be explained

Expected answer

The document needs to further explain what does Session{} and Session{NewDB: true} will do. And some sample code that's more production ready (like reuse the same connection, multiple queries in the same transaction, etc.)

[dingquan]

looks like .WithContext() is what I need to just clear the statement but keep the same db session

[zwell]

In GORM, if a struct already contains a non-zero primary key value, it will automatically add a WHERE clause using that primary key when executing queries like First(). This optimization can lead to unexpected behavior when reusing the same struct instance across multiple queries.

Example Behavior:

var user TestUser
user.ID = 1
db.First(&user) // Executes: WHERE id = 1

db.Where("id = 2").First(&user) 
// Unexpected: Executes WHERE id = 2 AND id = 1

user.ID = 0
db.Where("id = 2").First(&user) 
// Expected: Executes WHERE id = 2

Reason:
GORM optimizes queries by adding primary key conditions if the model has non-zero primary key values. When reusing the same variable, leftover values from previous queries may affect subsequent queries.

Solutions:

1. Use a new variable for each query to avoid carrying over old values.
2. Manually reset the primary key field (e.g., user.ID = 0) before the next query.

[bkline]

@zwell Can you provide a link to the documentation which explains that the First() method uses the first argument not just as the receptacle for the values in the retrieved row, but also as a source of additional restrictions to be added to the WHERE clause? I'm asking because:

1. The API documentation I was able to find just says that the method "finds the first record ordered by primary key, matching given conditions conds." The name of the first parameter is dest which would seem to imply that it's only used as the destination for the values.

2. The landing page for the official GORM documentation has a single short program showing a "Quick Start" for using the package. That program has two invocations of calls to db.First() one immediately after the other, with no code in between to clear out the values stuffed into the destination struct:

   db.First(&product, 1) // find product with integer primary key
   db.First(&product, "code = ?", "D42") // find product with code D42

   Notice that the comment for the second invocation doesn't say "find product with code D42 and primary key 1."

3. I've come across a published book on Go programming whose author was tripped up by this, with sample code which fails because it follows the pattern laid out by this sample. I won't defend the book's author too vigorously (I assume the code in the book didn't get tested), but I don't think all the blame falls on the author for this one. 😅

Personally, if this behavior is really intentional, I would regard it as a design flaw, as it violates the time-honored Principle of Least Astonishment in a number of ways. I mean, seriously, if the program already knows the primary key for the desired row, what would be the incentive for invoking the First() method with conditions?

[zwell]

API documentation I found that this section is mentioned in the documentation, though it's not very prominent.

Do you have any recommendations for optimizing this design?

[bkline]

Do you have any recommendations for optimizing this design?

That quoted passage appears in the informal general guides, not in the actual API Documentation itself published on the Go website.

My recommendation would be for the method to behave exactly as the API documentation says it will. The values of the first of the rows which match the conditions passed to the conds parameter are stored in the struct instance pointed to by the dest parameter, where "first" means "having the primary key with the lowest value."

[zwell]

I think the key issue is that the primary key is automatically added as a condition during queries, which can be confusing.

@jinzhu Maybe it would be better to add a configuration option for this behavior, or at least make it more explicitly documented in the API.