Gorm: Priority of <Or> and <And> query in query chains

[ltxdzh]

I am trying to make a chained query with a or query in it. Here is this model and my query (simplified for statement) :

type Assignment struct {
    User string
    Group string
    Project string
}
db.Where("user = ?", "user1").Or("group = ?", "group1").Where("project = ?", "project1").Find(&assignments)

The sql I expected is:

SELECT * FROM `assignments`  WHERE ((user = 'user1') OR (group = 'group1')) AND (project = 'project1')

But I got:

SELECT * FROM `assignments`  WHERE (user = 'user1') AND (project = 'project1') OR (group = 'group1')

Seems there is not way to increase the priority of Or query above And query in gorm.
Is there any development plan or any method I missed?

[rudeigerc]

You could use the following instead:

db.Where("user = ? OR group = ? OR project = ?", "user1", "group1", "project1").Find(&assignments)

[aditya101kumar]

One change in code base can fix above issue.

One sample query -

DbConnection.Order("created_at desc").Limit(limit).Where(Models.X{UserId: DbUtils.DbUtils{}.GetNullableString(ctx, UserId)}).Or(DbModels.X{TravellerPhoneNumber: DbUtils.DbUtils{}.GetNullableString(ctx, MobileNo)}).Not(DbModels.X{DbStatus: DbUtils.DbUtils{}.GetNullableString(ctx,"new")}).Find(&Data).Error

Here we are using where / Or / Not in a query.
When we use all the three query combined then the sql query which get generated is wrong.

eg - SELECT * FROM X WHERE X.deleted_at IS NULL AND ((X.user_id = '123456789') AND (X.db_status <> '7022376373') OR (X.phone_number = 'new')) ORDER BY created_at desc LIMIT 30

Here phone number should have a phone number and dbStatus should have "new" status.

Reason why this is happening - Go to scope.go file line no - 736

Below is the order in which we are building sql arguments

for _, clause := range scope.Search.whereConditions {
if sql := scope.buildCondition(clause, true); sql != "" {
andConditions = append(andConditions, sql)
}
}

for _, clause := range scope.Search.orConditions {
    if sql := scope.buildCondition(clause, true); sql != "" {
        orConditions = append(orConditions, sql)
    }
}

for _, clause := range scope.Search.notConditions {
    if sql := scope.buildCondition(clause, false); sql != "" {
        andConditions = append(andConditions, sql)
    }
}

it should be in below order -

for _, clause := range scope.Search.whereConditions {
if sql := scope.buildCondition(clause, true); sql != "" {
andConditions = append(andConditions, sql)
}
}

for _, clause := range scope.Search.notConditions {
if sql := scope.buildCondition(clause, false); sql != "" {
andConditions = append(andConditions, sql)
}
}

for _, clause := range scope.Search.orConditions {
    if sql := scope.buildCondition(clause, true); sql != "" {
        orConditions = append(orConditions, sql)
    }
}

i;e All andConditions should be appended once and then we should append oRConditions.

Post that the data which we have to put in the placeholder get parsed correctly -

orSQL := strings.Join(orConditions, " OR ")
combinedSQL := strings.Join(andConditions, " AND ")
if len(combinedSQL) > 0 {
if len(orSQL) > 0 {
combinedSQL = combinedSQL + " OR " + orSQL
}
} else {
combinedSQL = orSQL
}

[danielzheng]

same issue!

[fufuceng]

same issue!

[WhyLogin]

same issue!

[nobodyme]

Same issue

[github-actions[bot]]

This issue will be automatically closed because it is marked as GORM V1 issue, we have released the public testing GORM V2 release and its documents https://v2.gorm.io/docs/ already, the testing release has been used in some production services for a while, and going to release the final version in following weeks, we are still actively collecting feedback before it, please open a new issue for any suggestion or problem, thank you

Also check out https://github.com/go-gorm/gorm/wiki/GORM-V2-Release-Note-Draft for how to use the public testing version and its changelog