Gorm Is it possible to dynamically group conditions?

Your Question

Is it possible to dynamically group conditions?

The Grouped Conditions in the documentation are hard coded, but this is not an option in my use case since the conditions are dependent on user input.

I tried using scopes, but this did not work.

type generalFilter struct {
    field     string
    operation string
    value     string
}

filters := []generalFilter{
    {
        field:     "call_sign",
        operation: "=",
        value:     "dh",
    },
    {
        field:     "channel",
        operation: "=",
        value:     "5k",
    },
}

var scopes []func(*gorm.DB) *gorm.DB

for _, f := range filters {
    scopes = append(scopes, func(filter generalFilter) func(*gorm.DB) *gorm.DB {
        return func(tx *gorm.DB) *gorm.DB {
            return tx.Where(filter.field+" "+string(filter.operation)+" ?", filter.value)
        }
    }(f))
}

return db.Where(db.Scopes(scopes...))

Output was:

SELECT * FROM x WHERE channel = '5k' AND call_sign = 'dh' AND (channel = '5k' AND call_sign = 'dh')

The document you expected this should be explained

https://gorm.io/docs/advanced_query.html#Group-Conditions

Expected answer

Expected output is:

SELECT * FROM x WHERE (call_sign = 'dh' AND channel = '5k')

EDIT: Fixed scope creation in the loop

Comment From: pdavid31

Nevermind, I solved this problem.

The issue was in the code applying the scopes.

Comment From: rrupesh

@pdavid31 could you please post a sample working code?

Comment From: pdavid31

Hi @rrupesh,

I initially had some problems with scopes that where applied multiple times, so i just created an unscoped db session, applied the scopes to it and handed it to the Where clause.

This way it works, even though it's kinda hacky. Hope this helps you.

scopes := []func(*gorm.DB) *gorm.DB{
    // ...
}

// create an unscoped session instance to apply the scopes separately
tx := db.Session(&gorm.Session{NewDB: true}).Unscoped()

// apply the scopes in a where clause
db.Where(tx.Scopes(scopes...))

Comment From: sergevonage

Hi @pdavid31,

Interesting, I have same case and same problem, but your solution doesn't work me

func TestOR(t *testing.T) {
    db := dbSetup(t)
    dbOr1 := func(tx *gorm.DB) *gorm.DB {
        return tx.Or("id = ?", id1)
    }
    dbOr2 := func(tx *gorm.DB) *gorm.DB {
        return tx.Or("id = ?", id2)
    }
    var logs []models.Logs
    tx := db.Session(&gorm.Session{NewDB: true}).
        Unscoped()

    db.Where(tx.Scopes(dbOr1, dbOr2)).Find(&logs)
}

func dbSetup(t *testing.T) *gorm.DB {
...
        db, err := gorm.Open(sqlite.Open("file::memory:?cache=shared"), &gorm.Config{
        Logger: newLogger,
    })
...
}

Produces just

SELECT * FROM `logs`

instead of

SELECT * FROM `logs`WHERE (id = '1111' OR id = '2222' )

Comment From: pdavid31

@sergevonage I see you found a solution in #5052, so since my workaround is now already one year old I should have a look at that one :sunglasses: