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.