gorm: Error in generation groups queries in scopes

GORM Playground Link

Description

I tried to make SELECT * FROM "test" WHERE a = 1 AND (b = 2 OR c = 3)

but this request

DB.Table("test").Scopes(
  func(d *gorm.DB) *gorm.DB {
	  return d.Where("a = 1")
  },
  func(d *gorm.DB) *gorm.DB {
	  return d.Where(d.Or("b = 2").Or("c = 3"))
  },
).Rows()

makes sql SELECT * FROM "test" WHERE a = 1 OR b = 2 OR c = 3 AND (a = 1 OR b = 2 OR c = 3)

About this issue

Original URL
State: closed
Created a year ago
Comments: 15 (9 by maintainers)

Most upvoted comments

@black-06 This does not seem to be a scope problem, but a subquery processing problem. which will reuse Exprs to build a clause, even if it has been used externally. master/statement.go#L331
	tx := DB.Where("a = 1").Session(&gorm.Session{})
	tx.Where(tx.Or("b = 2").Or("c = 3")).First(&test)
	//  SELECT * FROM `tests` WHERE a = 1 AND (a = 1 OR b = 2 OR c = 3) AND `tests`.`deleted_at` IS NULL ORDER BY `tests`.`id` LIMIT 1
use a flag to make sure that expr only be used once?

It’s broken for
tx = db.Model(&User{}).Where(xxx)... // common conditions

tx.Where(o1).Find(&u1)
tx.Where(o2).Find(&u2)

Can we add such judgments? I didn’t test it, it’s just a possibility, when the DB instance is the same, the expression should have been added in the subquery.

if v != stmt.DB { 
	...
}

a631807682 on Mar 15, 2023

But it may require many changes to Scopes? Do you have any idea

Sorry, I only read your comment and didn’t check the question, I’ll check and get back to you.

a631807682 on Mar 14, 2023

It needs to be repaired and you can use this instead:

	db.Table("test").Scopes(
		func(d *gorm.DB) *gorm.DB {
			return d.Where("a = 1")
		},
		func(d *gorm.DB) *gorm.DB {
			return d.Where(db.Or("b = 2").Or("c = 3")) // it's db
		},
	).Rows()

black-06 on Mar 14, 2023