Gorm How to group conditions dynamically

Your Question

I have a graphql request base on which I need create query dynamically.

query MyQuery {
    Logs(where: {id: {_eq: "1111"}, _or: [{api_account_id: {_eq: "apiAccount1111"}}, {id: {_eq: "2222"}}]}) {
    message
    source_type
    id
  }
}

So I dynamically have to add WHERE id = ? and (api_account_id = ? OR id = ? ). OR condition I have to group before add.

The document you expected this should be explained

In a Group Conditions an example is just static query.

Expected answer

There is lack of examples about dynamic grouping query, in a for loop for example. I have tried example from another issue. But it didn't work out for me.

Comment From: li-jin-gou

hello @sergevonage https://gorm.io/docs/scopes.html maybe solve it

Comment From: sergevonage

hi @li-jin-gou, could you please guide me through an example:

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' )

What do I do wrong?

Comment From: li-jin-gou

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

db.Scopes(dbOr1, dbOr2).Find(&logs) and you should be able to construct scope dynamically based on your parameters.

Comment From: sergevonage

Thanks, @li-jin-gou . It helps. You example makes:

SELECT * FROM `logs` WHERE id = "1111" OR id = "2222"

But what I need is:

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

Because I want to group condition such as:

SELECT * FROM `logs` WHERE (id = "1111" OR id = "2222") AND api_account_id = "apiAcc1111"

not

SELECT * FROM `logs` WHERE id = "1111" OR id = "2222" AND api_account_id = "apiAcc1111"

Comment From: li-jin-gou

yes, so you need add some conditional judgements to implement your scope.

example:

func TestA(params string) func(tx *gorm.DB) *gorm.DB{
     return  func(tx *gorm.DB) *gorm.DB {
        return tx.Where(tx.Or("id = ?",prmas[0] ).Or(....))
    }
}

Comment From: sergevonage

@jinzhu but that's static conditions. How I can make it dynamic? Let's say I don't know how many OR I will have, but I know all these OR are one group

Comment From: li-jin-gou

@jinzhu but that's static conditions. How I can make it dynamic? Let's say I don't know how many OR I will have, but I know all these OR are one group

use loop ?

func TestA(paramslist string[]) func(tx *gorm.DB) *gorm.DB{
     return  func(tx *gorm.DB) *gorm.DB {
                tx1:= tx.Sesson(&gorm.Config())
                for _,pamras:= range paramslist{
                       tx1 = tx1.Or("id=?",params)
                }
         return tx.Where(tx1)
    }
}

Comment From: sergevonage

Thank you @li-jin-gou , your example worked out. But, does it mean I don't need scope? Just a condtion:

type condition struct {
    query    string
    argument interface{}
}
func TestA(conds condition[]) func(tx *gorm.DB) *gorm.DB{
     return  func(tx *gorm.DB) *gorm.DB {
                tx1:= tx.Session(&gorm.Session{NewDB: true})
                for _,cond:= range conds{
                       tx1 = tx1.Or(cond.query , cond.argument)
                }
         return tx.Where(tx1)
    }
}

Comment From: li-jin-gou

scope is used mainly to reuse code. If it's easy, you don't have to use it. :satisfied:

Comment From: sergevonage

I fully agree with you! I started with simple example such as:

db.Where(db.Or("id = ?", id1).Or("id = ?", id2))

then made it dynamic

for _,cond := range conds{ 
   db = db.Or(cond.query , cond.argument)
}
db.Where(db)

and it produced:

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

instead of

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

It think I missed that piece

tx:= db.Session(&gorm.Session{NewDB: true})

Comment From: rodrigovsilva

@sergevonage Have you tried using old gorm version "github.com/jinzhu/gorm"?

I believe this is only for new gorm, isn't it?

I've tried to use it and it doesn't work at all. Even the pizza example in this link https://gorm.io/docs/advanced_query.html#Group-Conditions

Comment From: li-jin-gou

@rodrigovsilva hello，jinzhu/gorm is not maintained anymore and many advanced features are not supported, so We recommend to upgrade gorm to gorm.io/gorm