Description
I'm trying to implement a dynamic query filter thorugh gorm scopes, when scope has simple condition it generates
correct result but when i want to set "Group Conditions" beside other simple conditions result is incorrect
For example for generating a query like :
select * from orders where id = "ID" and (order.side_r="A" or order.side_l="B")
through scopes its somthing like this :
func OrderIdFilter(value string) func(db gorm.DB) gorm.DB { return func(db gorm.DB) gorm.DB { return db.Where("id = ?", value) } } func OrderSideFilter(value string) func(db gorm.DB) gorm.DB { return func(db gorm.DB) gorm.DB { return db.Where(db.Where("side_r = ?", value).Or("side_l = ?", value)) } }
it generates:
SELECT * FROM "orders" WHERE orders.id = 'ID' AND side_r = 'A' OR side_l = 'B' AND (orders.id = 'ID' AND side_r = 'A' OR side_l = 'B' )
The document you expected this should be explained
https://gorm.io/docs/advanced_query.html#Group-Conditions
Expected answer is
SELECT * FROM "orders" WHERE orders.id = 'ID' AND (side_r = 'A' OR side_l = 'B')
Comment From: github-actions[bot]
The issue has been automatically marked as stale as it missing playground pull request link, which is important to help others understand your issue effectively and make sure the issue hasn't been fixed on latest master, checkout https://github.com/go-gorm/playground for details. it will be closed in 30 days if no further activity occurs. if you are asking question, please use the Question template, most likely your question already answered https://github.com/go-gorm/gorm/issues or described in the document https://gorm.io ✨ Search Before Asking ✨
Comment From: hemanth132
+1 , I am using gorm v1.22.4. Any update on this?
Comment From: github-actions[bot]
The issue has been automatically marked as stale as it missing playground pull request link, which is important to help others understand your issue effectively and make sure the issue hasn't been fixed on latest master, checkout https://github.com/go-gorm/playground for details. it will be closed in 30 days if no further activity occurs. if you are asking question, please use the Question template, most likely your question already answered https://github.com/go-gorm/gorm/issues or described in the document https://gorm.io ✨ Search Before Asking ✨
Comment From: hemanth132
@farzinft got the issue, when using chaining, you have to correctly pass the db. You will have to clone the db instance to pass the correct db to where condition
db1 = db.Session(&gorm.Session{NewDB: false})
db1.where(side_r = 'A').or(side_l = 'B')
db.where(db1).Find()
This should give the correct db statement.
Comment From: github-actions[bot]
The issue has been automatically marked as stale as it missing playground pull request link, which is important to help others understand your issue effectively and make sure the issue hasn't been fixed on latest master, checkout https://github.com/go-gorm/playground for details. it will be closed in 30 days if no further activity occurs. if you are asking question, please use the Question template, most likely your question already answered https://github.com/go-gorm/gorm/issues or described in the document https://gorm.io ✨ Search Before Asking ✨
Comment From: li-jin-gou
refer https://gorm.cn/docs/advanced_query.html#Group-Conditions
db.Where( DB.Where("pizza = ?", "pepperoni").Where(DB.Where("size = ?", "small").Or("size = ?", "medium")),).Or( DB.Where("pizza = ?", "hawaiian").Where("size = ?", "xlarge"),).Find(&Pizza{}).Statement
Note that the subcondition uses a new DB obj rather than a db.