why where condition is not in effect when I use Session()

[dorlolo]

Your Question

first, my codes like this

func main() {
	var data map[string]any
	DB := simdb.InitGormMysql(dbConfig)

	query1 := DB.Model(&User{})
	query2 := query1.Session(&gorm.Session{})

	query2.Where("id =100")
	query2.Debug().Find(&data)
}

then,lock at the debug info, where condition is lost.

[11.136ms] [rows:1] SELECT * FROM `sys_user`

finally, I've noticed this phenomenon, and I'm not sure if it's a bug.

func main() {
	var data map[string]any
	DB := simdb.InitGormMysql(dbConfig)

	query1 := DB.Model(&User{})
	query2 := query1.Session(&gorm.Session{})

	query1.Where("id = 1")
	query1.Debug().Find(&data)

	query2.Where("id =100")
	query2.Debug().Find(&data)
}

here is debug info, where condition of query2' is same as query1.

2025/01/08 09:40:20 D:/jjxu/work/demo/mytest/db/main.go:30
[2.614ms] [rows:0] SELECT * FROM `sys_user` WHERE id = 1

2025/01/08 09:40:20 D:/jjxu/work/demo/mytest/db/main.go:33
[3.182ms] [rows:0] SELECT * FROM `sys_user` WHERE id = 1

Usually, I writing it this way can resolve the issue.

func main() {
	var data map[string]any
	DB := simdb.InitGormMysql(dbConfig)

	query1 := DB.Model(&User{})
	query2 := query1.Session(&gorm.Session{})

	query2 = query2.Where("id =100")
	query2.Debug().Find(&data)
}

debug info:

[5.779ms] [rows:1] SELECT * FROM `sys_user` WHERE id =100

or

query1.Session(&gorm.Session{}).Debug().Where("id = 200").Find(&data)

Expected answer

This issue can make it difficult for beginners to get started. If it's not a bug, I hope it should be reflected in the documentation.

[adamqddnh]

This behavior is not a bug but an inherent characteristic of how GORM's query chaining and session handling work.

Why Does the WHERE Condition Disappear?

1. State Sharing Between Queries:

• In GORM, when you create a new query object (e.g., query2 := query1.Session(&gorm.Session{})), it inherits the state of query1.
• This inheritance includes shared query context but does not isolate the WHERE condition unless explicitly reset.

2. Chained Methods Don't Mutate Original Objects:

• GORM's query-building methods (e.g., Where) return a new query object instead of mutating the original. If you don't capture the returned value, the new query object (with updated WHERE conditions) is lost, and the original query remains unchanged.

3. Your Code Example:

• When you write query2.Where("id = 100"), it creates a new query object with the condition but doesn't reassign it to query2.
• As a result, the original query2 is used in query2.Debug().Find(&data), and its WHERE condition is lost.

How to Fix This
To avoid such issues, always capture the return value when modifying queries:

1. Reassign the Returned Query Object:

query2 = query2.Where("id = 100") // Explicit reassignment
query2.Debug().Find(&data)

2. Use Chainable Queries: If you don’t need intermediate queries, write everything in a single chain:

DB.Model(&User{}).Session(&gorm.Session{}).Where("id = 200").Debug().Find(&data)

3. Isolate State With Fresh Queries: Instead of reusing query1, start from DB.Model(&User{}) to ensure no shared state:

query1 := DB.Model(&User{}).Where("id = 1")
query1.Debug().Find(&data)

query2 := DB.Model(&User{}).Where("id = 100") // Fresh query
query2.Debug().Find(&data)