one to many join table

[malsoudani]

Hi everyone. I would like to ask is it possible to generate code for a one to many relationship modeled through a join table?

I know that typically people model one-to-many relationships using a foreign key on a table that is supposed belong to another table. But I have a situation where I have a lot of tables that are mutually exclusive to each other but all of the rows of these tables can have many rows associated to them in one specific table, lets call that table "x". any row from these tables can only have multiple x's and no other row from the same table can have association to the same x's, thus, each of those other tables I'm creating, I would end up creating a join table for to associate them to that x, but at the same time I would enforce a unique(x_id) constraint in those join table, resulting in a one (any row from these tables) to many x's relationship that is modeled through a join table. I would assume that if the sqlboiler can enforce many to many and can support one to many using foreign keys, then it should be able to support one to many through join tables? Do you guys think this would be a possible feature to add?

[stephenafamo]

I think I can maybe understand what you're trying to explain, but for clarity can you share a minimal DB schema?

[malsoudani]

@stephenafamo for sure:

say that I have the three following tables:

create table As {
    id uuid primary key not null default uuid_generate_v4(),
}

create table Bs {
    id uuid primary key not null default uuid_generate_v4(),
}

create table Xs {
    id uuid primary key not null default uuid_generate_v4(),
}

I want to model the following relationships:
an A can have many Xs / many Xs can belong to a single A
a B can have many Xs / many Xs can belong to a single B

I understand that the normal way of doing this is:

alter table Xs add column A_id uuid foreign key references As(id);
alter table Xs add column B_id uuid foreign key references Bs(id);

but because of my usecase, where my Xs can exists before my As and/or Bs and then they can be associated to either one later, I wanted to keep separate join/ junction tables so that I can do that association later:

create table A_Xs {
X_id uuid not null foreign key references Xs(id),
A_id uuid not null foreign key references As(id),
unique(X_id),
constraint pk_A_Xs primary key (X_id, A_id)
}

-- and then the same table for B table too.

basically I'm trying to do one to many using join tables, and given that many to many is supported and one to many is supported through direct foreign keys, would sqlboiler be able to support one to many using a join table by being able to detect the unique constraint on that table?

[stephenafamo]

I believe in some ORMs, this is referred to as "has many through".

At this time, this is not something SQLBoiler supports. Technically, it is possible to detect this with the information we already have, but someone would have to create a PR to actually do it and also craft the code that will be generated.

If you send a PR for this, I would be happy to take a look and merge.

[malsoudani]

Thank you @stephenafamo, once I have an MR I will send it your way