在GORM中按日期过滤

Question

I'm using GORM to access the records in my database. Now I want to retrieve all records that are not deleted which means, that the attribute DeletedAt must be NULL.

I tried the following command chains with WHERE(), but they returned no results.

users := []*models.User{}
db.Where("deleted_at", nil).Find(&users)

and

db.Where("deleted_at", "NULL").Find(&users)

My database model is defined by the following structs:

type Model struct {
    ID        uint `gorm:"primary_key"`
    CreatedAt time.Time
    UpdatedAt time.Time
    DeletedAt *time.Time
}

type User struct {
    gorm.Model
    Username string `sql:"size:32; not null; unique"`
    Password string `sql:"not null"`
    Locale   string `sql:"not null"`
}

Answer 1

With all RDBMS, the SQL standard mandates that a condition involving a comparison with a NULL value is always false. The following query therefore always returns an empty result:

select * from XXX where deleted_at = NULL

If you want to search for NULL values, you are supposed to write:

select * from XXX where deleted_at is null

I think you can fix the issue by making sure GORM generates the correct query. For instance, this should work (untested):

db.Where("deleted_at is null").Find(&users)