英文:
Filtering by date in GORM
问题
我正在使用GORM来访问数据库中的记录。现在我想检索所有未被删除的记录，也就是说，DeletedAt属性必须为NULL。
我尝试了以下使用WHERE()的命令链，但它们没有返回结果。
users := []*models.User{}
db.Where("deleted_at", nil).Find(&users)
和
db.Where("deleted_at", "NULL").Find(&users)
我的数据库模型由以下结构定义：
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"`
}
英文:
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.
<!-- language: lang-go -->
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"`
}
答案1
得分: 3
在所有关系型数据库管理系统（RDBMS）中，SQL标准规定与NULL值进行比较的条件始终为false。因此，以下查询始终返回空结果：
select * from XXX where deleted_at = NULL
如果你想搜索NULL值，应该写成：
select * from XXX where deleted_at is null
我认为你可以通过确保GORM生成正确的查询语句来解决这个问题。例如，下面的代码应该可以工作（未经测试）：
db.Where("deleted_at is null").Find(&users)
英文:
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)
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论