v0.22.0 - MySQL - Multi sided relationship won't compile

[jacobmolby]

In version 0.22.0 the following example doesn't compile.

It worked fine in 0.21. It has to do with a multi sided relation.

Given the following DDL:

--
-- Table structure for table `team_user`
--
CREATE TABLE `team_user` (
  `id` int NOT NULL AUTO_INCREMENT,
  `user_id` int DEFAULT NULL,
  `team_id` int DEFAULT NULL,
  PRIMARY KEY (`id`)
)
--
-- Table structure for table `teams`
--
CREATE TABLE `teams` (
  `id` int NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`)
)
--
-- Table structure for table `users`
--
CREATE TABLE `users` (
  `id` int NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`)
)

And the following bob config:

mysql:
  dsn: xxx

relationships:
  users:
    - name: "users_to_teams_through_team_users"
      by_join_table: true
      sides:
        - from: "users"
          to: "team_user"
          columns:
          - [id,user_id]
          to_unique: false
          to_key: true
        - from: "team_user"
          to: "teams"
          columns: 
          - [team_id,id]
          to_unique: true
          to_key: false

The error is the same with or without the by_join_table property. I'm not actually sure what it does. I don't think it's documented

Errors in teams.go

Errors in users.go

---

The generated factories and team_user.go doesn't have any compile errors.

[stephenafamo]

Thanks for such a detailed report.

Sadly, I don't have time to look into this immediately, but I'll find some time to look into it in the next couple weeks.

[jacobmolby]

Hi, are there any updates on this one?

If I should take a look at it myself, could you point me in a direction of which files/functions to look at? :)

[stephenafamo]

No updates yet.

If you want to take a look. The template that generates the code for this is in gen/templates/models/11_rel_ops.go.tpl and the template functions are in gen/templates.go:215

[jacobmolby]

I've tried to look at it, but I'm not smart enough to know how to "fix" it.

This is the commit that broke the "related through" functionality 9044d5b.

It seems that the code has been taken in a completely different direction, which makes it difficult for me to understand what should be changed/added to reintroduce the functionality.

I've also tried the same setup with postgres, which yields the same result.

[stephenafamo]

Yes, I realised that that was likely the offending commit, although it was added to fix something else.
I also understand this this part is hard to understand, it is perhaps the most complex part of the code generation.

Unfortunately, I cannot guarantee making time for this before the new year.

[jacobmolby]

I figured out that I if change processRelationshipConfig to make the middle table have the IsJoinTable = true then it seems to work. However I have no idea if that would break other functionality:

// in gen.go
// processRelationshipConfig checks any user included relationships and adds them to the tables
func processRelationshipConfig(config *Config, tables []drivers.Table) {
	if len(tables) == 0 {
		return
	}
	flipRelationships(config, tables)

	for i, t := range tables {
		rels, ok := config.Relationships[t.Key]
		if !ok {
			continue
		}

		tables[i].Relationships = mergeRelationships(tables[i].Relationships, rels)

		// Check if the current table is a join table. If thats the case we need
		// to set the ByJoinTable to true.
		for _, rel := range rels {
			// Potential join table
			if len(rel.Sides) > 1 {
				if rel.Sides[0].To == rel.Sides[1].From {
					// We have a join table. Find the table
					for j, t2 := range tables {
						if t2.Key == rel.Sides[0].To {
						        tables[j].IsJoinTable = true
							break
						}
					}
				}
			}
		}

	}
}

However, then I cannot make a direct relationship between one of either "users" or "teams" and "team_user".

relationships:
  users:
    - name: users_to_team_user
      sides:
        - from: "users"
          to: "team_user"
          columns: [[id,user_id]]
          to_key: true

This produces this error in "users.go"

However it is possible to define the inverse relationship (from "team_user" to "user")

  team_user: 
    - name: team_user_to_users
      no_reverse: true
      sides:
        - from: team_user
          to: users
          columns: [[user_id,id]]
          to_unique: true

[stephenafamo]

IsJoinTable is meant for a different idea and treats the entire table as "transparent". This is only true when all the columns of the join table are foreign keys to two other tables.

I'll say if this config works for you, perhaps you can use it until the root problem is fixed.

[jacobmolby]

Yea, I figured that it wasn't supposed to be hacked like that, by reading how the property is normally determined 😄

I might upgrade and lose the related through functionality, it just makes the queries a bit more annoying, but nothing crazy.

[stephenafamo]

So @jacobmolby I have been able to finally fix this. It affected a bunch of other things, so the changes are quite extensive.

1. Simplified relationship configuration

You no longer need to set from_unique, to_unique, to_key, to_nullable. These are gotten from the structure of the database as one may expect.

Technically, to_key is still useful, but it has been renamed to modify which can be "from" or "to". I think this is easier to mentally understand than to_key.
In addition, modify will now be inferred by some rules (outlined in the docs) which should work in most cases. But can still be manually configured to "from" or "to".

So your configuration can be simplified to this:

relationships:
  users:
    - name: "users_to_teams_through_team_users"
      sides:
        - from: "users"
          to: "team_user"
          columns:
          - [id,user_id]
        - from: "team_user"
          to: "teams"
          columns: 
          - [team_id,id]

2. Inferring join tables

With the above configuration, the generation should work and compile.
However, because team_users has additional columns, Bob is unable to treat it as a join table and will require you to provide a corresponding team_user for each user or team you want to insert or update.

The default generated code will look like this:

// In users.go
func (user0 *User) InsertTeams(ctx context.Context, exec bob.Executor, teamUsers1 TeamUserSlice, related ...*TeamSetter) error
func (user0 *User) AttachTeams(ctx context.Context, exec bob.Executor, teamUsers1 TeamUserSlice, related ...*Team) error

// In teams.go
func (team0 *Team) InsertUsers(ctx context.Context, exec bob.Executor, teamUsers1 TeamUserSlice, related ...*UserSetter) error 
func (team0 *Team) AttachUsers(ctx context.Context, exec bob.Executor, teamUsers1 TeamUserSlice, related ...*User) error

To make team_user be treated as a join table 2 conditions must be satisfied:

1. All columns in team_user must be mapped to a side. For cases like this, the way to make it work mapping of an empty column on either team or user to the id column of team_user.
2. There MUST be a unique constraint on the "relevant columns" i.e. team_id and user_id. You should either add this in the DB, or use the newly included constraints configuration to add one.

constraints:
  team_user:
    uniques:
      - name: "unique_team_id_user_id"
        columns: [team_id,user_id]

relationships:
  users:
    - name: "users_to_teams_through_team_users"
      sides:
        - from: "users"
          to: "team_user"
          columns:
          - [id,user_id]
          - ['',id] # The ghost mapping
        - from: "team_user"
          to: "teams"
          columns: 
          - [team_id,id]

This should generate the code you're likely expecting

[jacobmolby]

@stephenafamo Very cool! I don't have time to have an in depth look right now, but it looks great from what you've written. Thank you so much for your efforts. I really appreciate it.