Many many-to-many relationships to the same table causes naming conflicts

[aarondl]

Copied from original issue (#85):

@aarondl

1. Yes. I also built the dev branch but got the same result.
2. I can share the schema. This is the referenced table.

                                        Table "public.measurement_types"
       Column        |           Type           |                           Modifiers                            
---------------------+--------------------------+----------------------------------------------------------------
 id                  | integer                  | not null default nextval('measurement_types_id_seq'::regclass)
 num                 | integer                  | not null
 description         | text                     | not null
 deduction           | text                     | not null
 sum_factor          | numeric(16,8)            | default 1
 project_leader_only | boolean                  | default false
 created             | timestamp with time zone | default now()
 deleted             | timestamp with time zone | 
Indexes:
    "measurement_types_pkey" PRIMARY KEY, btree (id)
Referenced by:
    TABLE "material_mass_measurement_types" CONSTRAINT "material_mass_measurement_types_measurement_type_id_fkey" FOREIGN KEY (measurement_type_id) REFERENCES measurement_types(id)
    TABLE "service_add_mass_measurement_types" CONSTRAINT "service_add_mass_measurement_types_measurement_type_id_fkey" FOREIGN KEY (measurement_type_id) REFERENCES measurement_types(id)
    TABLE "service_mass_measurement_types" CONSTRAINT "service_measurement_types_measurement_type_id_fkey" FOREIGN KEY (measurement_type_id) REFERENCES measurement_types(id)

The three referencing tables are many-to-many join tables. This is the schema of one of them:

Table "public.service_mass_measurement_types"
       Column        |  Type   | Modifiers 
---------------------+---------+-----------
 service_id          | integer | not null
 measurement_type_id | integer | not null
Indexes:
    "service_measurement_types_pkey" PRIMARY KEY, btree (service_id, measurement_type_id)
    "service_measurement_types_measurement_type_id_idx" btree (measurement_type_id)
    "service_measurement_types_service_id_idx" btree (service_id)
Foreign-key constraints:
    "service_measurement_types_measurement_type_id_fkey" FOREIGN KEY (measurement_type_id) REFERENCES measurement_types(id)
    "service_measurement_types_service_id_fkey" FOREIGN KEY (service_id) REFERENCES services(id)

And this is the services table:

                                   Table "public.services"
          Column          |   Type   |                       Modifiers                       
--------------------------+----------+-------------------------------------------------------
 id                       | integer  | not null default nextval('services_id_seq'::regclass)
 revision_id              | integer  | not null
 service_category_id      | integer  | not null
 description              | text     | not null
 mass_unit                | text     | not null
 mass_unit_price          | bigint   | default 0
 add_mass_label           | text     | default ''::text
 add_mass_measurement     | text     | default ''::text
Indexes:
    "services_pkey" PRIMARY KEY, btree (id)
    "services_service_category_id_idx" btree (service_category_id)
Foreign-key constraints:
    "services_revision_id_fkey" FOREIGN KEY (revision_id) REFERENCES revisions(id)
    "services_service_category_id_fkey" FOREIGN KEY (service_category_id) REFERENCES service_categories(id)
Referenced by:
    TABLE "material_mass_measurement_types" CONSTRAINT "material_mass_measurement_types_service_id_fkey" FOREIGN KEY (service_id) REFERENCES services(id)
    TABLE "report_services" CONSTRAINT "report_services_service_id_fkey" FOREIGN KEY (service_id) REFERENCES services(id)
    TABLE "service_add_mass_measurement_types" CONSTRAINT "service_add_mass_measurement_types_service_id_fkey" FOREIGN KEY (service_id) REFERENCES services(id)
    TABLE "service_mass_measurement_types" CONSTRAINT "service_measurement_types_service_id_fkey" FOREIGN KEY (service_id) REFERENCES services(id)

(Some columns removed) A snippet from the generated code:

type measurementTypeR struct {                                                                                                           
          Services ServiceSlice                                                                                                            
          Services ServiceSlice                                                                                                            
          Services ServiceSlice                                                                                                            
 }

And all Service* methods on the MeasurementType type are declared 3 times.

3. Yes, I'm running Postgres 9.5.5

Thank you very much in advance for all the effort!

[aarondl]

@jgrossophoff The problem with this is simply that unlike the to-one and one-to-one relationships which use a foreign key name to aid in their uniqueness the many-to-many relationships do not.

I reproduced it with this schema:

create table mtypes (
  id serial not null primary key
);

create table other (
  id serial not null primary key
);

create table mjoin_one (
  mtype_id int not null references mtypes (id),
  other_id int not null references other (id),

  primary key (mtype_id, other_id)
);

create table mjoin_two (
  mtype_id int not null references mtypes (id),
  other_id int not null references other (id),

  primary key (mtype_id, other_id)
);

create table mjoin_three (
  mtype_id int not null references mtypes (id),
  other_id int not null references other (id),

  primary key (mtype_id, other_id)
);

After looking at your description it seemed like this was kind of what your schema looks like at it's core, and it reproduced the issue.

It's an oversight and we'll have to correct it somehow - but the result looks like it will be unpretty. Essentially we have to name the relationships differently, the only way to accomplish this in my head currently is to fix it by involving the name of the join table since this is the only unique piece of information that we have between all of these related things. Now obviously naming the relationship something like: serviceModel.ServiceMassMeasurementTypesMeasurementTypes() is fairly undesirable, so I'd propose we come up with some sort of naming convention as we have for to-many and to-one relationships. In that particular case we follow the rules outlined here: https://github.com/vattle/sqlboiler/blob/aab85fd2cde5468e3347c3c9e6430e6230bec2be/text_helpers.go#L170

This allows for brief names in the most common case which as described above is usually table_name_id as a foreign key which lets us identify the "primary" relationship between these two and lop off the additonal for-uniqueness-naming so instead of theModel.TableNameForeignKeyName you get theModel.TableName which is much nicer to use.

The first convention I think should obviously be the default name for a join table which should be some form of: first_table_second_table or of course second_table_first_table since the order is sort of subjective. In this case I think we should use the most basic name (the same we generate now).

We could go one step further and have a second convention that allows for a reduction in the named-that-way-for-uniqueness table names like you've created by extracting a chunk on the left, middle, or right of the above convention to use in the name. You could imagine a table named: services_mass_measurement_type and instead of using the entire name as well as the foreign table's name we could simply extract the word "mass" (in the middle of the two table names) and use that for uniqueness like so:

measurementTypeInstance.MassServices
service.MassMeasurementTypes

Keep in mind we'd probably enforce some pluralization around the naming conventions so your join table might end up being: services_mass_measurement_types. What do you think about this? Would this make SQLBoiler work for this use case?

[jgrossophoff]

Yes, that should work well for my use case. I've also thought about changing the schema of the join table to include a third column (maybe of type enum) that specifies the relationship type. Would that make it easier to support in sqlboiler?

[aarondl]

This scenario is already supported. Regardless we'll have to fix the above - but you could workaround with a single join table with a relationship type in the meantime. You'll get a much prettier names doing it that way.

[aarondl]

Talking about this more, we determined that it's a breaking change and that it will hurt existing SQLBoiler users. Therefore we have no choice but to defer to v3 so the fix will end up coming later. Sorry about that :) We care a lot about backwards compatibility.

[aarondl]

The generated Go names for Tables, Columns, and Relationships can now be aliased to prevent collisions: https://github.com/volatiletech/sqlboiler/tree/v3#names

I believe this tackles this scenario. I'd be very grateful if you'd try it out. Sorry for the long turnaround!