Gorm: 新手问题,关联其他数据表的操作。

[MikeJinhua]

我的数据结构这样的

type Player struct {
        ID   uint `gorm:"primary_key;AUTO_INCREMENT"`
    Name string `gorm:"not null;unique"`
    Cards []Card `gorm:"ForeignKey:PlayerID"`
}

type Card struct {
    PlayerID uint `gorm:"not null;unique"`
    CardCfgID uint `gorm:"not null;unique"`
    Num uint
}

1.如果想查询一个player的信息,如果一起查询出Cards要这样

var player Player
err = db.Where("Name = ?", "Mike"). Limit(1).Find(&player).Error
    if nil != err {
        fmt.Println(err)
    }
db.Model(&player).Related(&player.Cards)

有没有更简单的方式查出cards等关联信息呢。如果玩家关联很多别的属性，每个属性都加一行related有点麻烦
2看文档说默认保存关联的数据的我的没保存呢.我的代码不知道哪里问题，好像每个玩家只能存一张卡牌，cards加入第二张牌时候就存不上第二张了。 Player结构体里面cards的tag改成这样也一样的结果Cards []Card gorm:"many2many:Cards;"。
下面是保存player的代码。

        card1 := Card{PlayerID:player.ID,CardID:1}
    player.Cards = append(player.Cards, card1)
    card2 := Card{PlayerID:player.ID,CardID:2}
    player.Cards = append(player.Cards, card2)
    db.Set("gorm:save_associations", true).Save(&player)
    db.Save(player)

[Allendar]

Try using the ```go ``` tags around your code for better readability :)

[mykytanikitenko]

Please, can you use english? I'm from Ukraine, for me text in japan is unreadable. Especially because maybe someone will have the same question and will not have opportunity to find it in tracker.

[MikeJinhua]

ok,Mykytanikitenko.Let me translate my question into English in a few minutes.

[MikeJinhua]

Hi,mykytanikitenko.
Here is the English version.:-)
Here is my struct.

type Player struct {
        ID   uint `gorm:"primary_key;AUTO_INCREMENT"`
    Name string `gorm:"not null;unique"`
    Cards []Card `gorm:"ForeignKey:PlayerID"`
}

type Card struct {
    PlayerID uint `gorm:"not null;unique"`
    CardCfgID uint `gorm:"not null;unique"`
    Num uint
}

1.I want to query player info with player's cards info. Here is my code.

var player Player
err = db.Where("Name = ?", "Mike"). Limit(1).Find(&player).Error
    if nil != err {
        fmt.Println(err)
    }
db.Model(&player).Related(&player.Cards)

Is there having some easy way to query player info automatic with cards info,without additional code db.Model(&player).Related(&player.Cards)
2.The document said it will automatic save cards info when I save player info use db.save(player)
But now, I cannot save player's second card.
And it yet not work to change Cards tag to this Cards []Cardgorm:"many2many:Cards;"``
Here is my code

        card1 := Card{PlayerID:player.ID,CardCfgID :1}
    player.Cards = append(player.Cards, card1)
    card2 := Card{PlayerID:player.ID,CardCfgID :2}
    player.Cards = append(player.Cards, card2)
    db.Set("gorm:save_associations", true).Save(&player)
    db.Save(player)

There only one data of card1 in cards table of mysql.
And can you read my bad English?lol

[Allendar]

This should automatically save the associations you submit:

db.Model(&player).Association("Cards").Append(&card1, &card2)

[MikeJinhua]

多谢，Allendar.
我找到不能加入第二张卡片的原因了。
我需要的逻辑是，playerID和CardCfgID 共同做主键。
每个玩家都可以有很多种卡牌。只有playerID和CardCfgID同时重复才是错误的,其他情况应该都是正确的.
我现在的结构体tag配成了Card有两个主键，任意一个重复就报错，怎么才能把tag改成我需要的逻辑呢？**

---

Thx, Allendar.
I found the reason why I can not add the second card into table.
In my logic, I want to combine Card.PlayerID and Card.CardCfgID as one primary key.
Every player can have many kind of card in the cards table.Both playerID and CardCfgID are duplication is Error else is right.
But not the card table has two unique fields. How can I tag the card's field as my logic?

[Allendar]

It sounds like you're trying to achieve a Many2Many relationship? Many Players can have Many Cards? I'm not sure I understand on what level you want to constrict the relationship of the Player and it's Cards.

[MikeJinhua]

Thx for your reply.
I do not care what level of relationship of db.I just want to finish requirement lol.
I know for higher level db, I must have Player table, Card Kind table,and Player and Card Kind relationship table.
But in my game, Card Kind table is not in db . It a const configuration which is read by code and limit by code.
So I design structs like this.can you help me.

type Player struct {
        ID   uint `gorm:"primary_key;AUTO_INCREMENT"`
    Name string `gorm:"not null;unique"`
    Cards []Card `gorm:"ForeignKey:PlayerID"`
}

type Card struct {// It's a Player and Card Kind relation table.
    PlayerID uint //`half key,I do not know how to fill it"`
    CardCfgID uint //`"another half key,I do not know how to fill it"`
    Num uint
}

[MikeJinhua]

Anyway,I make my requirement clear now. Can you help me replace the placeholder?
``go type Player struct { ID uintgorm:"primary_key;AUTO_INCREMENT" Name stringgorm:"not null;unique" Cards []PlayerCardKindRelationgorm:"many2many:Cards;"`
}

type PlayerCardKindRelation struct {
PlayerID uint//xxxxx?????????????? placeholder 1. it's foreign key of Player.ID
CardID uint//xxxxx?????????????? placeholder 2. it's foreign key of CardKind.cardKindID
Num uint
}

type CardKind struct{
cardKindID uint gorm:"primary_key;AUTO_INCREMENT"
}
````

[Allendar]

It's very good to use constants as type-identifiers, as long as you know for sure they will never be needed in deeper relationship-combinations.

If I understand you right, you want to know how many Cards a Player would be having of a certain type of card?

I think your spec would look something like this:

type Player struct {
    gorm.Model
    Name string `gorm:"not null;unique"`
    Cards []PlayersCards
}

type Card struct {
    gorm.Model
    Name string `gorm:"not null"` // e.a. Knights of the Round Table
}

type CardKind struct{
    gorm.Model
    Name string `gorm:"not null"` // e.a. Legendary Elder
}

type PlayersCards struct {
    gorm.Model
    Player Player
    PlayerID uint `gorm:"not null"`
    Card Card
    CardID uint `gorm:"not null"`
    Num uint // Amount? or is this meant for something else?
}

Sorry if I'm understand you wrong. If you want control over your Many2Many table (pivot) you need to make it n2M:M2n yourself (PlayersCards) and it's then not a M2M anymore for GORM.

In the above example you do something like player.Cards.Related("Card", "card_kind_id = ?", cardKingID). You should try out what suits for you tho. Maybe I'm totally going off track now :P

[MikeJinhua]

Sounds good,and thank you.
let me have a try.

[MikeJinhua]

I have two new problems,now.
First there have a error when I save player.

panic: reflect.Value.Addr of unaddressable value

goroutine 1 [running]:
reflect.Value.Addr(0x6b4860, 0xc042082c40, 0x99, 0x5, 0x0, 0x0)
    D:/Go/src/reflect/value.go:239 +0xba
github.com/jinzhu/gorm.queryCallback(0xc0424acc80)
    D:/GoPath/src/github.com/jinzhu/gorm/callback_query.go:70 +0x3d8
github.com/jinzhu/gorm.(*Scope).callCallbacks(0xc0424acc80, 0xc042107fc0, 0x3, 0x4, 0xc0424acc80)
    D:/GoPath/src/github.com/jinzhu/gorm/scope.go:821 +0x53
github.com/jinzhu/gorm.(*DB).First(0xc042075b00, 0x6b4860, 0xc042082c40, 0x0, 0x0, 0x0, 0x5451b6)
    D:/GoPath/src/github.com/jinzhu/gorm/main.go:271 +0x1e2
github.com/jinzhu/gorm.(*DB).FirstOrCreate(0xc042075b00, 0x6b4860, 0xc042082c40, 0x0, 0x0, 0x0, 0xc04202bd40)
    D:/GoPath/src/github.com/jinzhu/gorm/main.go:353 +0x86
github.com/jinzhu/gorm.(*DB).Save(0xc04213a000, 0x6b4860, 0xc042082c40, 0xc042082c40)
    D:/GoPath/src/github.com/jinzhu/gorm/main.go:397 +0x125
main.Test()
    D:/GoProject1/server_db_examplenew/src/server/main.go:90 +0xac0
main.main()
    D:/GoProject1/server_db_examplenew/src/server/main.go:11 +0x27

Second, how to limit player_id and card_id don't duplicate at the same times in players_cards table.It's Composite Primary Key.
My game database struct is very similar to Clash Royale game. Players collect cards.Does it help you know what I want to do lol.

Here's my code lol.

type Player struct {
    gorm.Model
    Name string `gorm:"not null;unique"`
    Cards []PlayersCards `gorm:"many2many:PlayersCards;"`
}

type PlayersCards struct {
    gorm.Model
    Player Player
    PlayerID uint `gorm:"not null"`//Composite Primary Key ，foreign key of Payer.id
    Card Card
    CardID uint `gorm:"not null"`//Composite Primary Key ，foreign key of Card.id
    Amount uint
    Level uint `gorm:"default:'1'"`
}

type Card struct {
    ID uint `gorm:"primary_key"`
    Name string `gorm:"not null"` // e.a. Knights of the Round Table
}

func Test() {
    db, err := gorm.Open("mysql", "mike:123456@tcp(localhost:3306)/gorm?parseTime=true")
    defer db.Close()
    if err != nil {
        panic("connect db error")
    }
    defer db.Close()

    // 自动迁移模式
    db.AutoMigrate(&Player{})
    db.AutoMigrate(&Card{})
    db.AutoMigrate(&PlayersCards{})

    // create player named mike
    err1 := db.Create(&Player{
        Name: "Mike",
    }).Error
    if nil != err1 {
        fmt.Println("already exist:",err1)
    }

    // create card with id 1 and name goblin
    err1  = db.Create(&Card{
        ID:1,
        Name: "goblin",
    }).Error
    if nil != err1 {
        fmt.Println("already exist:",err1)
    }

    // create card with id 2 and name Archer
    err1  = db.Create(&Card{
        ID:2,
        Name: "Archer",
    }).Error
    if nil != err1 {
        fmt.Println("already exist:",err1)
    }

    //query player Mike
    var player Player
    err = db.Where("Name = ?", "Mike"). Limit(1).Find(&player).Error
    if nil != err {
        fmt.Println(err)
    }
    db.Model(&player).Related(&player.Cards)

    //mike's has 10 goblin cards.
    playersCard1 := PlayersCards{PlayerID:player.ID,CardID:1,Amount:10}
    player.Cards = append(player.Cards, playersCard1)
    //mike's has 5 Archer cards.
    playersCard2 := PlayersCards{PlayerID:player.ID,CardID:2,Amount:5}
    player.Cards = append(player.Cards, playersCard2)
    //db.Model(&player).Association("Cards").Replace(player.Cards)
    db.Save(player)
}

[MikeJinhua]

I found this problem https://github.com/jinzhu/gorm/issues/1037
But I do not know how to fix it.

[mykytanikitenko]

Don't use composite primary keys for associative m2m tables, just create unique(player_id, card_id) constraint

[MikeJinhua]

How to mark go unique(player_id, card_id) in struct

type PlayersCards struct {
    gorm.Model
    PlayerID uint `gorm:"not null;unique"`
    CardID uint `gorm:"not null;unique"`
    Amount uint`gorm:"default:'0'"`
    Level uint `gorm:"default:'1'"`
} 

It's
UNIQUE KEY player_id (player_id),
UNIQUE KEY card_id (card_id),
in sql,
but not unique(player_id, card_id)

[MikeJinhua]

It work for unique(player_id,card_id)

type Player struct {
    ID    uint `gorm:"primary_key"`
    Name string `gorm:"not null;unique"`
    Cards []PlayersCards
}

type PlayersCards struct {
    gorm.Model
    PlayerID uint `gorm:"unique_index:idx_name_code"`
    CardID uint `gorm:"unique_index:idx_name_code"`
    Amount uint`gorm:"default:'0'"`
    Level uint `gorm:"default:'1'"`
}

But how to relation player.cards with PlayersCards .playerid.
db.Model(&player).Related(&player.Cards) not work, and db.Save(player) crash.

[Allendar]

I mainly avoid defining combined indexes on the objects. Try;

err := db.Model(&PlayersCards{}).AddUniqueIndex(
    "uidx_player_card",
    "player_id", "card_id",
).Error
if nil != err {
    panic(err)
}

[MikeJinhua]

Thank you very much.

[MikeJinhua]

Problem solved now.
I use golang code solved associations and only use gorm some base function .

[iinux]

@mykytanikitenko The language which Mikejinhua first written is Chinese not Japanese

[langgo]

@Mikejinhua

下面这个可能能解决你的第一个问题。

preload

db.Preload("Orders").Find(&users)
//// SELECT * FROM users;
//// SELECT * FROM orders WHERE user_id IN (1,2,3,4);

db.Preload("Orders", "state NOT IN (?)", "cancelled").Find(&users)
//// SELECT * FROM users;
//// SELECT * FROM orders WHERE user_id IN (1,2,3,4) AND state NOT IN ('cancelled');

db.Where("state = ?", "active").Preload("Orders", "state NOT IN (?)", "cancelled").Find(&users)
//// SELECT * FROM users WHERE state = 'active';
//// SELECT * FROM orders WHERE user_id IN (1,2) AND state NOT IN ('cancelled');

db.Preload("Orders").Preload("Profile").Preload("Role").Find(&users)
//// SELECT * FROM users;
//// SELECT * FROM orders WHERE user_id IN (1,2,3,4); // has many
//// SELECT * FROM profiles WHERE user_id IN (1,2,3,4); // has one
//// SELECT * FROM roles WHERE id IN (4,5,6); // belongs to

[xdxiaodong]

Don't use composite primary keys for associative m2m tables, just create unique(player_id, card_id) constraint

This is Chinese