GORM v2 is on going

[jinzhu]

Hello All,

GORM v2 is under active development (https://github.com/jinzhu/gorm/tree/v2_dev), going to release in the next two months.

Before that, I am NOT going to merge any pull requests based on the master branch.

V2 will be overwritten from scratch with similar public API, it would focus on performance, improve usability and fix fragile designs. We will provide a migration guide to help users migrate before the release.

With the new architecture, it opens the possibility to have a better migration tool, a static code generator that generates type-safe code with all the features of GORM, visualizes models and its relationships to help a new team member quickly understand your project, and even support Redis & MongoDB...

Your code review or suggestions would be much appreciated, please comment to this thread, thank you.

[popsUlfr]

Sure but shouldn't the scanner be able to infer the date from the number ?

Like time.Unix(1592475552, 0)

One is free to store the time as text, real or integer in an sqlite database :

• TEXT as ISO8601 strings ("YYYY-MM-DD HH:MM:SS.SSS").
• REAL as Julian day numbers, the number of days since noon in Greenwich on November 24, 4714 B.C. according to the proleptic Gregorian calendar.
• INTEGER as Unix Time, the number of seconds since 1970-01-01 00:00:00 UTC.

[leefernandes]

To store jsonb in v1 I could add a gorm tag of type jsonb, and implement Scan/Value on the struct.

This no longer works with the migrator in v2 because the CREATE TABLE query is missing the type of the column.

pq: at or near ",": syntax error

CREATE TABLE "harumphs" ("id" bigserial,"created_at" timestamptz,"updated_at" timestamptz,"deleted_at" timestamptz,"embedded" ,PRIMARY KEY ("id"))

type Harumph struct {
    gorm.Model
    Embedded `gorm:"type:jsonb;"`
}

type Embedded struct {
    Content interface{}
}

In v2 I also must implement GormDBDataType(db *gorm.DB, field *schema.Field) string for the type to be defined in CREATE TABLE, or embed datatypes.JSON. The new features in datatypes.JSON looks very neat 👍, just want to report a breaking change from v1 json.

[jinzhu]

To store jsonb in v1 I could add a gorm tag of type jsonb, and implement Scan/Value on the struct.

This no longer works with the migrator in v2 because the CREATE TABLE query is missing the type of the column.

pq: at or near ",": syntax error

CREATE TABLE "harumphs" ("id" bigserial,"created_at" timestamptz,"updated_at" timestamptz,"deleted_at" timestamptz,"embedded" ,PRIMARY KEY ("id"))

type Harumph struct {
  gorm.Model
  Embedded `gorm:"type:jsonb;"`
}

type Embedded struct {
  Content interface{}
}

In v2 I also must implement GormDBDataType(db *gorm.DB, field *schema.Field) string for the type to be defined in CREATE TABLE, or embed datatypes.JSON. The new features in datatypes.JSON looks very neat 👍, just want to report a breaking change from v1 json.

Upgrade PostgreSQL driver to v0.2.0 should fix this.

[jinzhu]

Sure but shouldn't the scanner be able to infer the date from the number ?

Like time.Unix(1592475552, 0)

One is free to store the time as text, real or integer in an sqlite database :

• TEXT as ISO8601 strings ("YYYY-MM-DD HH:MM:SS.SSS").
• REAL as Julian day numbers, the number of days since noon in Greenwich on November 24, 4714 B.C. according to the proleptic Gregorian calendar.
• INTEGER as Unix Time, the number of seconds since 1970-01-01 00:00:00 UTC.

You can just use int with tag autoupdatetime

https://github.com/go-gorm/gorm/wiki/GORM-V2-Release-Note-Draft#multiple-fields-support-for-auto-creatingupdating-time-which-also-support-unix-nano-seconds-when-the-data-type-is-int

---

btw, error 'Scan, storing driver.Value type int64 into type *time.Time' returns from sql/database package as it doesn't support it

[leefernandes]

Upgrade PostgreSQL driver to v0.2.0 should fix this.

Thanks 👍 this works

// go.mod

gorm.io/driver/postgres v0.2.0 h1:Q93Q+keJ7rg0nDvCbjOGJ1ze8wEg86k00ofD70QRCTg=
gorm.io/driver/postgres v0.2.0/go.mod h1:nKAMTWR3HotfKKWcK4A/ZrxU55IqzONLyCxeXyHdzfA=
gorm.io/gorm v0.2.7 h1:hpXSTXLQfOUaITMjnQJ2ah+0uuQJLMsKcLytfqdteBw=
gorm.io/gorm v0.2.7/go.mod h1:0HFTzE/SqkGTzK6TlDPPQbAYCluiVvhzoA1+aVyzenw=

[leefernandes]

@jinzhu in v2 is there a way to specify a many2many foreignkey column name on the jointable?

in v1 I was able to specify the column name on the jointable with association_jointable_foreignkey like this

type Harumph struct {
    gorm.Model
    Relationships []Harumph `gorm:"many2many:harumph_relationships;association_jointable_foreignkey:custom_column_name"`
}

which would create this table

[jinzhu]

@jinzhu in v2 is there a way to specify a many2many foreignkey column name on the jointable?

in v1 I was able to specify the column name on the jointable with association_jointable_foreignkey like this

type Harumph struct {
  gorm.Model
  Relationships []Harumph `gorm:"many2many:harumph_relationships;association_jointable_foreignkey:custom_column_name"`
}

which would create this table

https://github.com/go-gorm/gorm/wiki/GORM-V2-Release-Note-Draft#association-improvements

[leefernandes]

I missed the comment for JoinForeignKey

// Many2Many: ForeignKey specifies the current model's primary key, JoinForeignKey specifies join table's foreign key that refers to ForeignKey // References specifies the association's primary key, JoinReferences specifies join table's foreign key that refers to References

This works

type Harumph struct {
    gorm.Model
    Relationships []Harumph `gorm:"many2many:harumph_relationships;ForeignKey:id;JoinForeignKey:CustomColumnName;"`
}

[leefernandes]

Is there anything in place to get schema.Field info in v2? It looks like NewScope() has gone away

I'm looking for v1 functionality akin to

scope := db.NewScope(myModel)
field, ok := scope.FieldByName(fieldName)
dbFieldName := field.DBName

I see that schema is stored in cacheStore, but I haven't found a way to either pass in a cacheStore (its unexported in gorm.Config) or get the cacheStore from gorm.

In the meantime I'm doing this, but it would probably be more efficient to lookup from the cacheStore which gorm has already populated:

cacheStore := &sync.Map{}

schema, err := schema.Parse(doc, cacheStore, db.NamingStrategy)
if err != nil {
    return nil, err
}

field := schema.LookUpField(fieldName)
if nil == field {
    return nil, fmt.Errorf("unsupported field '%s'", fieldName)
}

[leefernandes]

Seeing a difficult to explain breaking change around Scan & Value with embedded structs.

When implementing Value on an embedded struct, the fields are no longer created as columns for the outer struct by the migrator, and can encounter some infinite recursion on the Valuer method during Create.

type Outer struct {
    gorm.Model
    OuterField      string
    OuterJSON interface{} `gorm:"type:jsonb;"`
    Inner
}

type Inner struct {
    InnerField       string
    InnerJSON interface{} `gorm:"type:jsonb;"`
}

func (m Inner) Value() (driver.Value, error) {
    fmt.Println("Inner Value", m)
    if v, err := json.Marshal(m.InnerJSON); err != nil {
        return nil, err
    } else {
        m.InnerJSON = v
    }

    return m, nil
}

func (m *Inner) Scan(src interface{}) error {
    fmt.Println("Inner Scan", src)
    b, ok := src.([]byte)
    if !ok {
        return errors.New("Inner.Scan byte assertion failed")
    }

    var value Inner
    if err := json.Unmarshal(b, &value); err != nil {
        return err
    }

    *m = value

    return nil
}

harumph := Outer{
    OuterField: "abc",
    OuterJSON: map[string]interface{}{
        "outer": "thing",
    },
    Inner: Inner{
        InnerField: "xyz",
        InnerJSON: map[string]interface{}{
            "inner": "stuff",
        },
    },
}

if err := db.Save(&harumph).Error; err != nil {
    log.Fatal(err)
}

var harumphs []Outer

if err := db.Find(&harumphs).Error; err != nil {
    log.Fatal(err)
}

Do you prefer reports as new Issues, or continue using this issue?

[jinzhu]

Is there anything in place to get schema.Field info in v2? It looks like NewScope() has gone away

https://github.com/go-gorm/gorm/blob/07960fe661b5ced50c9ca30e010aa26513eaf851/tests/migrate_test.go#L140-L151

[jinzhu]

func (m Inner) Value() (driver.Value, error) {
    fmt.Println("Inner Value", m)
    if v, err := json.Marshal(m.InnerJSON); err != nil {
        return nil, err
    } else {
        m.InnerJSON = v
    }

    return m, nil
}

It should returns m. InnerJSON but not itself?

---

Do you prefer reports as new Issues, or continue using this issue?

Let open new issues as this one is too long...

[mostafaznv]

is there any chance to support nestedset in v2 ?

[cnsilvan]

3065 Is there such a method? use inline condition for updating method(like db.Updates(updates, " xxx= ?", "xxx")))?

[jinzhu]

@mostafaznv @heww

Added SavePoint, RollbackTo, Nested Transaction Support

https://github.com/go-gorm/gorm/wiki/GORM-V2-Release-Note-Draft#savepoint-rollbackto-support

https://github.com/go-gorm/gorm/wiki/GORM-V2-Release-Note-Draft#nested-transaction-support

[mostafaznv]

@jinzhu thanks for reply. but I didn't mean nested transaction. It was nestedset (or hierarchy, or tree, or flat parent/child structure).

please see this: https://github.com/vcraescu/gorm-nested

additional information: https://en.m.wikipedia.org/wiki/Nested_set_model

I wanted to create tree for category model but gorm didn't handle it. I think it would be perfect if gorm handle it in next release.

[jinzhu]

@jinzhu thanks for reply. but I didn't mean nested transaction. It was nestedset (or hierarchy, or tree, or flat parent/child structure).

please see this: https://github.com/vcraescu/gorm-nested

additional information: https://en.m.wikipedia.org/wiki/Nested_set_model

I wanted to create tree for category model but gorm didn't handle it. I think it would be perfect if gorm handle it in next release.

sounds like single-table association, refer: https://github.com/go-gorm/gorm/blob/e3292b3b4171cefe59694391729aa997640cc92e/utils/tests/models.go#L14-L26

[mostafaznv]

Yes, I think so. But how we can get ancestors and descendants? what about tree structure? Are we using modified preorder tree traversal algorithm to fetch all children (tree)?

sounds like single-table association, refer:

https://github.com/go-gorm/gorm/blob/e3292b3b4171cefe59694391729aa997640cc92e/utils/tests/models.go#L14-L26

[jinzhu]

But how we can get ancestors and descendants? what about tree structure?

You could use Preload to get ancestors and descendants, e.g:

If you know it is a three-levels structure, then:

db.Preload("Children.Children.Children").Find(&tags)

And you can use Association Mode to manage its data, if you want more focused methods, I would suggest making a package like acts as tree to do the job.

[RichardLindhout]

@mostafaznv I don' think GORM should handle it to be honest.

If your tree is not that big the best way is to cache it since it will be much faster than multiple queries to fetch the hierarchy

E.g.

type Cache struct {
    db         *gorm.DB
    byID       map[string]*YOURGORMMODEL
    byParentID map[string][]string
    up         map[string][]string
}

GetTreeUp()
GetTreeDown()

[mostafaznv]

@RichardLindhout Maybe it's not GORM responsibility but I don't want to fetch the hierarchy with multiple queries (it's not a good approach). I want to fetch it with one query (using lft, rgt method) and cache it for boost up performance. I'm new in golang and came up from php/laravel community. I used this method there. I wanted to implement it here in golang but I have very low experience in golang package development and it takes me time to handle it ...

[jinzhu]

Syntax to group conditions something akin to the following would be nice to see in v2

db.Where(
  db.Where("pizza = ?", "pepperoni").And(db.Where("size = ?", "small").Or("size = ?", "medium")),
).Or(
  db.Where("pizza = ?", "hawaiian").And("size = ?", "xlarge"),
).Find(&pizzas)

output:

select * from pizzas where (pizza = 'pepperoni' and (size = 'small' or size = 'medium')) or (pizza = 'hawaiian' and size = 'xlarge')

upperdb demonstrates a neat implementation for composing conditions. https://upper.io/db.v3/getting-started#composing-conditions-db-or-and-db-and

db.And(
  db.And(
    db.Cond{"age >": 21},
    db.Cond{"age <": 28},
  ),
  db.Or(
    db.Cond{"name": "Joanna"},
    db.Cond{"name": "John"},
    db.Cond{"name": "Jhon"},
  ),
)

( (age > 21 AND age < 28) AND (name = 'Joanna' OR name = 'John' OR name = 'Jhon') )

And hopefully composed conditions would support complex subqueries, not just basic operands/operators.

@ItsLeeOwen Supported https://github.com/go-gorm/gorm/commit/3d8f6f9cf9e225c964c66634b6b34df8e139f792#diff-5668006f0830727adf964efbee0c1a3bR143

[ahmadfarisfs]

Thank you for having context and foreign key improved @jinzhu 😋

[leefernandes]

@jinzhu is there any callback in v2 to mutate models after retrieval from database, similar to Scan?

[ahmadfarisfs]

AutoMigrate Many2many does not add foreignkey to join table

type User struct {
    gorm.Model
    FirstName string    `gorm:"not null" json:"firstname" validate:"required"`
    LastName  string    `gorm:"not null" json:"lastname" validate:"required"`
    Email     string    `gorm:"not null;unique" json:"email" validate:"required,email"`
    Phone     string    `gorm:"not null;unique" json:"phone"`
    RoleID    int       `gorm:"not null" json:"role"`
    Password  string    `gorm:"not null" json:"-"`
    Projects  []Project `gorm:"many2many:user_projects;ForeignKey:id;References:id"`
}
type Project struct {
    gorm.Model
    Name        string `gorm:"not null" json:"name" validate:"required"`
    ProjectType string `gorm:"not null;type:enum('onetime','business_unit')" json:"project_type"`
    Status      string `json:"status"`
}
db.Set("gorm:table_options", "ENGINE=InnoDB").AutoMigrate(&domain.Project{}, &domain.User{})

this resulted in table user_projects created but with no foreign key, also user_projects created using default engine (MyISAM as my mariaDB engine default setting, not overrides by db.Set()), but the other table (projects and users) created with InnoDB.

[axetroy]

consider supporting FOR UPDATE and FOR UPDATE NOWAIT for a row lock

select * from db_user where name='lisi' for update;
select * from db_user where name='lisi' for update nowait;

db.Model(user).Where("name = ?", "list").ForUpdate(true /* true = nowait OPTIONAL */).First(&user)

[jinzhu]

@jinzhu is there any callback in v2 to mutate models after retrieval from database, similar to Scan?

ScanRows? https://github.com/go-gorm/gorm/blob/master/finisher_api.go#L335

[jinzhu]

consider supporting FOR UPDATE and FOR UPDATE NOWAIT for a row lock

select * from db_user where name='lisi' for update;
select * from db_user where name='lisi' for update nowait;

db.Model(user).Where("name = ?", "list").ForUpdate(true /* true = nowait OPTIONAL */).First(&user)

DB.Clauses(clause.Locking{Strength: "UPDATE"}).Find(&users) // SELECT * FROM `users` FOR UPDATE

DB.Clauses(clause.Locking{
  Strength: "SHARE",
  Table: clause.Table{Name: clause.CurrentTable},
}).Find(&users)
// SELECT * FROM `users` FOR SHARE OF `users`

https://github.com/go-gorm/gorm/wiki/GORM-V2-Release-Note-Draft#locking

[axetroy]

Another thing, is it uniform (standard) capitalization rules

I have found that in some places, uppercase and lowercase are used, and there seems to be no uniform standard

type Blog struct {
  ID         uint   `gorm:"primary_key"`
  Locale     string `gorm:"primary_key"`
  Subject    string
  Body       string
  // 默认用使用对象的全部主键 (ID, Locale) 来创建关联表
  Tags       []Tag `gorm:"many2many:blog_tags;"`
  // 对于 Blog, Tag 都将只使用 ID 做为主键
  SharedTags []Tag `gorm:"many2many:shared_blog_tags;ForeignKey:id;References:id"`
  // 对于 Blog 使用ID, Locale作为主键, Tag 只使用ID做为主键
  LocaleTags []Tag `gorm:"many2many:locale_blog_tags;ForeignKey:id,locale;References:id"`
}

many2many/primary_key/unique/index is lowercase References/ForeignKey is uppercase

same with var style. why ForeignKey, not foreign_key?

These are easy to confuse how to use, I hope there is a unified rule and style

[jinzhu]

AutoMigrate Many2many does not add foreignkey to join table

type User struct {
  gorm.Model
  FirstName string    `gorm:"not null" json:"firstname" validate:"required"`
  LastName  string    `gorm:"not null" json:"lastname" validate:"required"`
  Email     string    `gorm:"not null;unique" json:"email" validate:"required,email"`
  Phone     string    `gorm:"not null;unique" json:"phone"`
  RoleID    int       `gorm:"not null" json:"role"`
  Password  string    `gorm:"not null" json:"-"`
  Projects  []Project `gorm:"many2many:user_projects;ForeignKey:id;References:id"`
}
type Project struct {
  gorm.Model
  Name        string `gorm:"not null" json:"name" validate:"required"`
  ProjectType string `gorm:"not null;type:enum('onetime','business_unit')" json:"project_type"`
  Status      string `json:"status"`
}
db.Set("gorm:table_options", "ENGINE=InnoDB").AutoMigrate(&domain.Project{}, &domain.User{})

this resulted in table user_projects created but with no foreign key, also user_projects created using default engine (MyISAM as my mariaDB engine default setting, not overrides by db.Set()), but the other table (projects and users) created with InnoDB.

Fixed, please upgrade to v0.2.11

[jinzhu]

Another thing, is it uniform (standard) capitalization rules

I have found that in some places, uppercase and lowercase are used, and there seems to be no uniform standard

type Blog struct {
  ID         uint   `gorm:"primary_key"`
  Locale     string `gorm:"primary_key"`
  Subject    string
  Body       string
  // 默认用使用对象的全部主键 (ID, Locale) 来创建关联表
  Tags       []Tag `gorm:"many2many:blog_tags;"`
  // 对于 Blog, Tag 都将只使用 ID 做为主键
  SharedTags []Tag `gorm:"many2many:shared_blog_tags;ForeignKey:id;References:id"`
  // 对于 Blog 使用ID, Locale作为主键, Tag 只使用ID做为主键
  LocaleTags []Tag `gorm:"many2many:locale_blog_tags;ForeignKey:id,locale;References:id"`
}

many2many/primary_key/unique/index is lowercase References/ForeignKey is uppercase

same with var style. why ForeignKey, not foreign_key?

These are easy to confuse how to use, I hope there is a unified rule and style

Cases don't matter, so both foreignkey and ForeignKey are ok

primary_key is just for compatibility, recommend to use PrimaryKey, and you should use CamelCase–style for all other tags.

[axetroy]

Another thing, is it uniform (standard) capitalization rules I have found that in some places, uppercase and lowercase are used, and there seems to be no uniform standard

type Blog struct {
  ID         uint   `gorm:"primary_key"`
  Locale     string `gorm:"primary_key"`
  Subject    string
  Body       string
  // 默认用使用对象的全部主键 (ID, Locale) 来创建关联表
  Tags       []Tag `gorm:"many2many:blog_tags;"`
  // 对于 Blog, Tag 都将只使用 ID 做为主键
  SharedTags []Tag `gorm:"many2many:shared_blog_tags;ForeignKey:id;References:id"`
  // 对于 Blog 使用ID, Locale作为主键, Tag 只使用ID做为主键
  LocaleTags []Tag `gorm:"many2many:locale_blog_tags;ForeignKey:id,locale;References:id"`
}

many2many/primary_key/unique/index is lowercase References/ForeignKey is uppercase same with var style. why ForeignKey, not foreign_key? These are easy to confuse how to use, I hope there is a unified rule and style

Cases don't matter, so both foreignkey and ForeignKey are ok

primary_key is just for compatibility, recommend to use PrimaryKey, and you should use CamelCase–style for all other tags.

So we need to give a clear explanation in the document, which style to use and this should reduce developer doubts

BTW: lower camel case is diff wtih upper camel case

index > Index unique > Unique many2many > Many2Many

[jinzhu]

So we need to give a clear explanation in the document, which style to use and this should reduce developer doubts

Yes, will explain it when rewriting the document

BTW: lower camel case is diff wtih upper camel case

GORM itself doesn't care cases, but I would prefer the upper camel case

[ahmadfarisfs]

Hi, running this code : ` //db is *gorm.DB and already opened db = db.Order("id desc") db.Model(result).Count(&count) db.Limit(p.Limit).Offset(offset).Find(result)

` produce undesirable output for the second query (second query still has count(1) ) :

2020/06/21 23:15:33 D:/Gits/mrkrab-be/utilities/pagination.go:61 [5.928ms] [rows:1] SELECT count(1) FROM users WHERE users.deleted_at IS NULL ORDER BY id desc

2020/06/21 23:15:33 D:/Gits/mrkrab-be/utilities/pagination.go:69 [0.713ms] [rows:1] SELECT count(1) FROM users WHERE users.deleted_at IS NULL AND users.deleted_at IS NULL ORDER BY id desc LIMIT 10

[jinzhu]

Hi, running this code : ` //db is *gorm.DB and already opened db = db.Order("id desc") db.Model(result).Count(&count) db.Limit(p.Limit).Offset(offset).Find(result)

` produce undesirable output for the second query (second query still has count(1) ) :

2020/06/21 23:15:33 D:/Gits/mrkrab-be/utilities/pagination.go:61 [5.928ms] [rows:1] SELECT count(1) FROM users WHERE users.deleted_at IS NULL ORDER BY id desc 2020/06/21 23:15:33 D:/Gits/mrkrab-be/utilities/pagination.go:69 [0.713ms] [rows:1] SELECT count(1) FROM users WHERE users.deleted_at IS NULL AND users.deleted_at IS NULL ORDER BY id desc LIMIT 10

Hello @ahmadfarisfs

If you want to share a DB like this, you should use db = db.Order("id desc").Session(&Session{WithConditions:true}).

BTW, Count with Order doesn't work with some db drivers, any reason write code like this?

[ahmadfarisfs]

Hi, running this code : //db is *gorm.DB and already opened db = db.Order("id desc") db.Model(result).Count(&count) db.Limit(p.Limit).Offset(offset).Find(result) produce undesirable output for the second query (second query still has count(1) ) :

2020/06/21 23:15:33 D:/Gits/mrkrab-be/utilities/pagination.go:61 [5.928ms] [rows:1] SELECT count(1) FROM users WHERE users.deleted_at IS NULL ORDER BY id desc 2020/06/21 23:15:33 D:/Gits/mrkrab-be/utilities/pagination.go:69 [0.713ms] [rows:1] SELECT count(1) FROM users WHERE users.deleted_at IS NULL AND users.deleted_at IS NULL ORDER BY id desc LIMIT 10

Hello @ahmadfarisfs

If you want to share a DB like this, you should use db = db.Order("id desc").Session(&Session{WithConditions:true}).

BTW, Count with Order doesn't work with some db drivers, any reason write code like this?

Oh i see. I tried to use this (works on v1): https://github.com/biezhi/gorm-paginator using gorm v2, apparently its not plug and play. Thanks @jinzhu !

[leefernandes]

@jinzhu Does v2 have any support for specifying sql column enums based on a Go enum? Where provided the below scenario, CREATE TABLE would include the enums?

type MyEnum string

const (
    EnumA MyEnum = "a"
    EnumB MyEnum = "b"
    EnumC MyEnum = "c"
)

type MyModel struct {
    gorm.Model
    Type MyEnum
}

Instead of manually defining them on the struct tag?

type MyModel struct {
    gorm.Model
    Type MyEnum `gorm:"type:enum('a','b','c');"`
}

[jinzhu]

@jinzhu Does v2 have any support for specifying sql column enums based on a Go enum? Where provided the below scenario, CREATE TABLE would include the enums?

type MyEnum string

const (
  EnumA MyEnum = "a"
  EnumB MyEnum = "b"
  EnumC MyEnum = "c"
)

type MyModel struct {
  gorm.Model
  Type MyEnum
}

Instead of manually defining them on the struct tag?

type MyModel struct {
  gorm.Model
  Type MyEnum `gorm:"type:enum('a','b','c');"`
}

Hi, GORM can't know defined MyEnum values, but you need to set the type with tag.

But I think it is better to define a method GormDataType for MyEnum, and it returns above type, refer: https://github.com/go-gorm/datatypes/blob/546ecd30e4baf6e1f33085e185ebb8bf8b7d8235/json.go#L53 for example

[cnsilvan]

Hi, running this code : //db is *gorm.DB and already opened db = db.Order("id desc") db.Model(result).Count(&count) db.Limit(p.Limit).Offset(offset).Find(result) produce undesirable output for the second query (second query still has count(1) ) :

2020/06/21 23:15:33 D:/Gits/mrkrab-be/utilities/pagination.go:61 [5.928ms] [rows:1] SELECT count(1) FROM users WHERE users.deleted_at IS NULL ORDER BY id desc 2020/06/21 23:15:33 D:/Gits/mrkrab-be/utilities/pagination.go:69 [0.713ms] [rows:1] SELECT count(1) FROM users WHERE users.deleted_at IS NULL AND users.deleted_at IS NULL ORDER BY id desc LIMIT 10

Hello @ahmadfarisfs

If you want to share a DB like this, you should use db = db.Order("id desc").Session(&Session{WithConditions:true}).

BTW, Count with Order doesn't work with some db drivers, any reason write code like this?

@jinzhu I did what you said, but the result is still the same

orm=orm.Table(xxx).Session(&gorm.Session{WithConditions: true})
orm = orm.Where("name= ?","use111")
orm = orm.Count(&total).Offset(int(page * req.Limit)).Limit(20).Find(&users)

//SQL:
SELECT count(1) FROM `xxx` WHERE  xxx.`name` = ("use111")
//Why? 
SELECT count(1) FROM `xxx` WHERE  xxx.`name` = ("use111") LIMIT 10,20

3065

[jinzhu]

tx :=orm.Table(xxx).Where("name= ?","use111").Session(&gorm.Session{WithConditions: true})
tx.Count(&total)
tx.Offset(int(page * req.Limit)).Limit(20).Find(&users)

[cnsilvan]

tx :=orm.Table(xxx).Where("name= ?","use111").Session(&gorm.Session{WithConditions: true})
tx.Count(&total)
tx.Offset(int(page * req.Limit)).Limit(20).Find(&users)

thanks

[efrengarcial]

Hi @jinzhu , I don't know what I'm doing wrong, but "total" always returns zero (0):

package main

import (
    "fmt"
    "log"
    "os"
    "time"

    "gorm.io/driver/mysql"
    "gorm.io/gorm"
    "gorm.io/gorm/logger"
)

type User struct {
    Id   int
    Name string
    Date time.Time
}

var Default = logger.New(log.New(os.Stdout, "\r\n", log.LstdFlags), logger.Config{
    SlowThreshold: 100 * time.Millisecond,
    LogLevel:      logger.Info,
    Colorful:      true,
})

func main() {
    dsn := "root:password@tcp(localhost:3306)/db?charset=utf8&parseTime=True&loc=Local"
    db, _ := gorm.Open(mysql.Open(dsn), &gorm.Config{Logger: Default})

    _ = db.Migrator().DropTable(&User{})

    _ = db.AutoMigrate(&User{})

    now := time.Date(2010, 1, 1, 1, 1, 1, 1, time.Now().Location())
    users := []*User{{Name: "mark", Date: now},
        {Name: "jhon", Date: now}}
    err := db.Create(&users).Error
    fmt.Println(err)

    var total int64
    db = db.Table("users").Where("name= ?", "mark").Session(&gorm.Session{WithConditions: true})
    db.Count(&total)
    fmt.Println(total)

    db.Order("id desc").Limit(10).Offset(0).Find(&users)
    fmt.Println(users[0])

}

[jinzhu]

Hi @jinzhu , I don't know what I'm doing wrong, but "total" always returns zero (0):

package main

import (
  "fmt"
  "log"
  "os"
  "time"

  "gorm.io/driver/mysql"
  "gorm.io/gorm"
  "gorm.io/gorm/logger"
)

type User struct {
  Id   int
  Name string
  Date time.Time
}

var Default = logger.New(log.New(os.Stdout, "\r\n", log.LstdFlags), logger.Config{
  SlowThreshold: 100 * time.Millisecond,
  LogLevel:      logger.Info,
  Colorful:      true,
})

func main() {
  dsn := "root:password@tcp(localhost:3306)/db?charset=utf8&parseTime=True&loc=Local"
  db, _ := gorm.Open(mysql.Open(dsn), &gorm.Config{Logger: Default})

  _ = db.Migrator().DropTable(&User{})

  _ = db.AutoMigrate(&User{})

  now := time.Date(2010, 1, 1, 1, 1, 1, 1, time.Now().Location())
  users := []*User{{Name: "mark", Date: now},
      {Name: "jhon", Date: now}}
  err := db.Create(&users).Error
  fmt.Println(err)

  var total int64
  db = db.Table("users").Where("name= ?", "mark").Session(&gorm.Session{WithConditions: true})
  db.Count(&total)
  fmt.Println(total)

  db.Order("id desc").Limit(10).Offset(0).Find(&users)
  fmt.Println(users[0])

}

Fixed, thank you for your report, please upgrade to v0.2.15

[pioz]

@jinzhu in v2 how can I get the model table name? Before I can do db.NewScope(&user).TableName(), but now?

[pioz]

@jinzhu I've tried to change the log level with db.Config.Logger.LogMode(logger.Silent) but I still see the gorm log messages on stdout. If I change the logger in this way, all works as expected.

db.Config.Logger = logger.New(log.New(os.Stdout, "\r\n", log.LstdFlags), logger.Config{
  SlowThreshold: 100 * time.Millisecond,
  LogLevel:      logger.Silent,
  Colorful:      true,
})

[jinzhu]

@jinzhu in v2 how can I get the model table name? Before I can do db.NewScope(&user).TableName(), but now?

Hi @pioz, you can do it like this, btw, could you let me know your use case?

stmt := &gorm.Statement{DB: DB}
stmt.Parse(value)
stmt.Table

[jinzhu]

@jinzhu I've tried to change the log level with db.Config.Logger.LogMode(logger.Silent) but I still see the gorm log messages on stdout.

Change LogMode will create a new logger to avoid affect other projects using the same default logger, refer: https://github.com/go-gorm/gorm/blob/7e1fa4a44de7b1febfc5620cab4afe77276b4a72/logger/logger.go#L102-L106

So you can change the logger like:

db.Config.Logger = db.Config.Logger.LogMode(logger.Silent)

[seaguest]

I tried to immigrate from jinzhu to go-gorm, I did get a lot of errors, missing methods (some turned out in other package, ex HasTable...), some are really missing

    db.DB().SetMaxIdleConns(50)
    db.DB().SetMaxOpenConns(1500)

Another problem is the code is kind of mixed github.com and gorm.io,

        db.Logger.LogMode(logger.Info)

github.com/go-gorm/gorm/gorm.go imports

    "gorm.io/gorm/clause"
    "gorm.io/gorm/logger"
    "gorm.io/gorm/schema"

Finally I gave up. when it will be ready for production for this go-gorm?

[ipfans]

@seaguest db.DB() returns two results includes error message. Maybe you are using outdated gorm v2?

[blakejia]

Can the array and map in postgresql be fully supported?