Dynamically-computed field is ignored or generates an error on row insertion

[Zebra345]

Your Question

Using packages gorm.io/gorm v1.23.8 gorm.io/driver/postgres v1.3.9

I'm trying to run the following SQL command using GORM:
SELECT *, (SELECT SUM(count) FROM crash_counts WHERE crash_id = crashes.id) AS total FROM crashes;
and have the result stored in an array of Crash structs.

Here, "total" is a dynamically-computed field, i.e. not an actual column in the table.
"total" is not in the table because it must be computed on-the-fly from data in another table.
Then the info is passed along to be displayed in HTML and discarded.

There are two rows in the DB, and the SQL command returns 12 & 3 for the totals.

I tested the 3 documented "ignore this field" permissions:

Totalx  uint `gorm:"-"`
Totaly  uint `gorm:"-:all"`
Totalz  uint `gorm:"-:migration"`

I run:

query = query.Debug().Select("*, (?) AS totalx, (?) AS totaly, (?) AS totalz",
    database.DB.Table("crash_counts").Select("SUM(count)").Where("crash_id = crashes.id"),
    database.DB.Table("crash_counts").Select("SUM(count)").Where("crash_id = crashes.id"),
    database.DB.Table("crash_counts").Select("SUM(count)").Where("crash_id = crashes.id"))
query.Find(&Crashes)
fmt.Printf("%v\n", Crashes)

And I get 0 0 12 0 0 3 for totalx totaly totalz
I don't understand why only totalz is correct and why totalx & totaly are 0?

Related issue: when I try to insert a new row, I get the following error:
ERROR: column "totalz" of relation "crashes" does not exist (SQLSTATE 42703)

So totalz gets updated correctly for Find(), but it generates an error on row insertion.

How would I define the "total" field to have both use-cases functional?

The document you expected this should be explained

https://gorm.io/docs/models.html

Advanced
Field-Level Permission
  Name string `gorm:"-"`            // ignore this field when write and read with struct
  Name string `gorm:"-:all"`        // ignore this field when write, read and migrate with struct
  Name string `gorm:"-:migration"`  // ignore this field when migrate with struct

Expected answer

I think the gorm:"-:all" tag should work in my use-case.

1. Never create the field in the table
2. Update the field in the Find query
3. Ignore the field in row insertion or reads.

What am I doing wrong? :(

[jinzhu]

Hi @Zebra345

Can you check the generated SQL? seems like the error not related to GORM tags

Please create a reproducible playground pull request

[jinzhu]

Or maybe you can try to create readonly fields, refer https://gorm.io/docs/models.html#Field-Level-Permission

[Zebra345]

Hello @jinzhu
I worked around the issue by defining an auxiliary struct used specifically for the query.
I tested the 10 Field-Level Permissions, none of them worked for my use-case.
Regards