DB Query Commands are producing strange sql queries

Question

I have my model as:

type Report struct {
    ID    int     `json:"id,omitempty" gorm:"primary_key"`
    Title *string `json:"title" gorm:"not null"`
}

I have initialized variable report as var report Report I have successfully auto migrated this model as database table and have populated database as sql INSERT using GORM's db.Create(&report).

The problem I am facing is while trying query commands. Every query commands supported by GORM such as db.Find(&report) , db.First(&report, 1) is resulting to queries such as folows:

SELECT * FROM "reports"  WHERE "reports"."deleted_at" IS NULL AND ((id = $1))
SELECT * FROM "reports"  WHERE "reports"."deleted_at" IS NULL AND ((id = $1))
SELECT * FROM reports WHERE (reports.deleted_at IS NULL) AND ((id = $1))
SELECT * FROM reports WHERE (reports.deleted_at IS NULL) AND ((id = $1))
SELECT 0 done

I am unable to query database. I am using GORM with cockroach db. This works fine when using GO pq driver and raw sql commands.

score 2 · Answer 1 · answered Jul 24 '17 at 14:35

2

The deleted_at column is a part of GORM's base gorm.Model struct and its soft delete feature. Are you using gorm.Model somewhere that we can't see in this example? This isn't supposed to happen unless you either define a field named DeletedAt or embed a gorm.Model in your model struct.

answered Jul 24 '17 at 14:35

Ben Darnell

21,844
3
29
50

exactly what I am confused with. I have not defined any field named `DeletedAt` nor I'm using `gorm.Model`. I have only defined one `Report struct` and wrapped gorm under `httprouter` to insert and query each. `db.create` works fine. using error function in `db.Find` returns `record not found`. Btw I derived my code closely based on example-orm package provided by cockroachlabs. – 1tree Jul 24 '17 at 16:21
I tried again but could not figure out the problem. Examples provided as **Query With Where (Struct & Map)** in GORM's documentation also did not work. Meanwhile, I went with GORM's raw query as `db.Raw("SELECT * FROM reports").Scan(&report)` and only this seems to work at the moment. – 1tree Jul 24 '17 at 18:18

score 1 · Answer 2 · answered Aug 28 '19 at 16:26

1

Since the model has the field deleted_at, gorm is using the soft delete ability automatically. You can use Unscoped

 db.Unscoped().Find(&reports)

Which is the same as running the raw query

db.Raw("SELECT * FROM reports").Scan(&reports)

answered Aug 28 '19 at 16:26

edkeveked

17,989
10
55
93

DB Query Commands are producing strange sql queries

2 Answers2