Pg: Specifying fk for join table struct

[noggan]

I have database relation set up like:

type Team struct {
    TableName struct{} `sql:"teams,alias:team" json:"-"`

    Id TeamId `sql:",pk" json:"id"`

    TournamentJoinRequests []TournamentJoinRequest `pg:"team,many2many:tournament_team_join_requests,joinFK:team" json:"-"`
}

type TournamentJoinRequest struct {
    TableName struct{} `sql:"tournament_team_join_requests" json:"-"`

    TeamId        TeamId                 `sql:",pk" json:"-"`
    Team          *Team                  `json:"-"`
    TournamentId  TournamentId           `sql:",pk" json:"-"`
    Tournament    *Tournament            `json:"-"`
}

When I'm querying the database like the following:

Db.Model(&team).Where("team.id = ?", id).Column("team.*", "TournamentJoinRequests").Select()

I keep receiving the following error: tournament_team_join_requests.team_team_id does not exist.

How can I specify the foreign key for it to properly try to access the team_id column ?

[vmihailenco]

Please check m2m example again to understand how it works. I did not try, but it should look like

type Team struct {
    Id TeamId
    Tournaments []Tournament `pg:"many2many:tournament_team_join_requests"`
}

type Tournament struct {
    Id TournamentId
    Name string // tournament name
    Teams []Team `pg:"many2many:tournament_team_join_requests"`
}

type TournamentTeamJoinRequest struct {
    TeamId        TeamId                 `sql:",pk" json:"-"`
    Team          *Team                  `json:"-"`
    TournamentId  TournamentId           `sql:",pk" json:"-"`
    Tournament    *Tournament            `json:"-"`
}

The biggest mistake is that should specify the struct/table you are joining, not itermediate struct/table []TournamentJoinRequest -> []Tournament

[noggan]

Sorry I was trying to abstract what I'm trying to do too much so the question became something else than I intended. The database actually looks like this:

type Team struct {
    TableName struct{} `sql:"teams,alias:team" json:"-"`

    Id TeamId `sql:",pk" json:"id"`

    Users []PublicUserInformation `pg:",many2many:team_user_ref,joinFK:user" json:"users"`
    Tournaments []Tournament `pg:",many2many:tournament_team_ref" json:"-"`
    TournamentJoinRequests []TournamentJoinRequest `pg:"team,many2many:tournament_team_join_requests,joinFK:team" json:"-"`
}

type TournamentJoinRequest struct {
    TableName struct{} `sql:"tournament_team_join_requests" json:"-"`

    TeamId TeamId `sql:",pk" json:"-"`
    Team *Team `json:"-"`
    UserId UserId `sql:",pk" json:"-"`
    User *User `json:"-"`
    TournamentId TournamentId `sql:",pk" json:"-"`
    Tournament *Tournament `json:"-"`
}

So the join table actually has a relation to three tables (users, teams and tournaments) because a team sends invites to all it's member for a given tournament and I want to track which users of a team joined a specific tournament. So basically I want the TournamentJoinRequests field to load both users and tournaments relation for a given team which is why I added the struct from the start and I think I got fooled by it working since it did not throw any errors until I added the sql:,pk tag to all ids.

I resolved the issue by adding a repository function that retrieves this data using the TournamentJoinRequest struct instead. But just for reference is there a way to load a relation like this into a single struct (when using Column("TournamentJoinRequests") on Team model)?

Feel free to close the issue when you feel like it.

[vmihailenco]

Probably something like this will work (but hard to say without trying):

type Team struct {
    Id TeamId
    JoinRequests []TournamentTeamJoinRequest
}

type TournamentTeamJoinRequest struct {
    TeamId        TeamId                 `sql:",pk" json:"-"`
    Team          *Team                  `json:"-"`
    TournamentId  TournamentId           `sql:",pk" json:"-"`
    Tournament    *Tournament            `json:"-"`
}

[Terminator637]

Probably something like this will work (but hard to say without trying):

type Team struct {
  Id TeamId
  JoinRequests []TournamentTeamJoinRequest
}

type TournamentTeamJoinRequest struct {
  TeamId        TeamId                 `sql:",pk" json:"-"`
  Team          *Team                  `json:"-"`
  TournamentId  TournamentId           `sql:",pk" json:"-"`
  Tournament    *Tournament            `json:"-"`
}

Yes! It works Perfectly!

[mnmistake]

so this is like having a many to many relation? @vmihailenco