CODEWITHSUNDEEP
gogo-gorm
stelios
stelios

Reputation: 2845

Go-gorm returns all records if filtered with Struct instance that has default Value in a field

Lets say we have the following struct:

type Task struct {
    ...
    Completed bool      `gorm:"default:false"                  json:"-"`
}

There are 5 entries in the MySQL DB:

I face the following peculiarity:

db, err = gorm.Open("mysql", connstr)
var ret []Task

// This returns 3 rows
db.Debug().Model(&Task{}).Find(&ret, "Completed =?", false)
// This returns 2 rows
db.Debug().Model(&Task{}).Find(&ret, Task{Completed: true})
// BUT THIS RETURNS 5 ROWS!
db.Debug().Model(&Task{}).Find(&ret, Task{Completed: false})

Any ideas why is this happening? The SQL executed at the last call was: SELECT * FROM 'tasks'

I would like to avoid writing a new SQL query for each struct field. Passing the struct (object) seems more sensible.

Upvotes: 2

Views: 1404

Answers (1)

robbieperry22
robbieperry22

Reputation: 2245

Reason

GORM looks at the fields of the struct provided to Find(), and checks to see which fields are set, to construct the query. So in the 2nd example, it sees that Completed is set to true, and adds a WHERE clause to the query:

db.Debug().Model(&Task{}).Find(&ret, Task{Completed: true})

generates:

SELECT * FROM "tasks" WHERE "tasks"."completed" = true

In the second example, GORM has no way of knowing if you passed in Task{Completed: false} or simply Task{} because false is the zero-value of a bool.

The same thing would happen if you had a field that was an int and tried to query for 0:

db.Debug().Model(&Task{}).Find(&ret, Task{Num: 0}) // Generates: SELECT * FROM 'tasks'

Solution

If you really want to use the struct for queries, you can change your model so that Completed is a *bool instead of a bool. Since the zero-value of a pointer is nil, providing a pointer to false will tell GORM to add that clause:

trueBool := true
falseBool := false

db.Debug().Model(&Task{}).Find(&ret, Task{Completed: &falseBool})
db.Debug().Model(&Task{}).Find(&ret, Task{Completed: &trueBool})

generate (respectively)

SELECT * FROM "tasks" WHERE "tasks"."completed" = false
SELECT * FROM "tasks" WHERE "tasks"."completed" = true

Just remember that doing this means that Completed can be saved in the DB as NULL if not set.

Upvotes: 3

Related Questions