Gorm: How can i implement complex query filter condition such as : a and b and (c or d or e)

[leon-db]

after i read all the guide document, i am still confuse how cloud i implement complex query condition:
such as:

select * from test where a_cond and b_cond and (c_cond or d_cond or e_cond)

except to use:
db.where(a_cond, arg1).where(b_cond, arg2).where("c_cond or d_cond or e_cond", args3, args4, args5)

[ermik]

Can you show the data model you are using (the struct)?

[evan-xie]

type User struct {
Id string
Mobile string
Email string
Username string
Password string
Status int
Locked int
}

I want to get the result like:
select * from user where status = 1 and locked = 0 and (mobile = '888888' or email = '[email protected]' or username = 'test') order by id ASC LIMIT 1

[ermik]

(I realize the following is exactly what @matrixstylite has a problem with)
Let's break it down:

• select * from user — this is default behavior when you use .Find()
• order by id ASC LIMIT 1 — this is what .First() does, use it instead of .Find
• where — you can use .Raw() for complex behaviors; to use more of the ORM you should use .Where()
• where status = 1 and locked = 0 — do it like this:

.Where("status = ?", statusInt).Where("locked = ?", lockedInt)

• and (mobile = '888888' or email = '[email protected]' or username = 'test') — can still use .Where():

.Where("mobile = ? or email = ? or username = ?", mobileNum, emailAddr, userName)

So in full, it should be something like:

var u = User{}
err := db.Where("status = ?", statusInt)
         .Where("locked = ?", lockedInt)
         .Where(
             "mobile = ? or email = ? or username = ?",
             mobileNum,
             emailAddr,
             userName,
         )
         .First(&u)
         .Error
if err != nil {
     // handle db errors
}

// at this point "user" variable should have the found user or remain empty if nothing was found

[evan-xie]

func AndOrs(query *gorm.DB, m map[string]interface{}) (*gorm.DB) {
    if m == nil {
        return query
    }

    str := ""
    args := make([]interface{},0,len(m))

    for k,v := range m {
        str = str + k + " = ? or "
        args = append(args,v)
    }

    str = string([]rune(str)[:len(str)-4])

    return query.Where(str,args...)
}

    query := db.Where("status = ?",1)

    m := map[string]interface{}{
        "mobile":u.Mobile,
        "email":u.Email,
        "username":u.Username,
        "id":u.Id,
    }

    err := utils.AndOrs(query,m).First(&u).Error

[ermik]

That's really cool! I see that we looped on @matrixstylite's question and I provided the solution that he specifically asked how to avoid.

I think that Where is something we write if we do raw SQL, and here it shouldn't be avoided. It's verbosity provides clarity to the code, ease of readability is improved by specifying these conditions openly, rather than with some ORM sugar. There is room for helper methods though, like the one @evan-xie has posted.

[leon-db]

the question i ask because i use a repository pattern base on gorm, thanks all you guys

[zbird]

Seems there is no eligant solution for a and (b or c) query.

[mattdamon108]

@evan-xie This is awesome. It helps me a lot.