Generating models fails with many-to-many on same table

[jseriff]

We have a table which can reference back to itself in a many to many relationship. When generating the schema, we get the an error.

Here are the (abbreviated) tables in question, pulled from a database dump:

CREATE TABLE industries (
    id integer NOT NULL,
    name text
);

ALTER TABLE ONLY industries
    ADD CONSTRAINT industries_pkey PRIMARY KEY (id);

CREATE TABLE industries_industries (
    industry_id integer NOT NULL,
    mapped_industry_id integer NOT NULL,
    relation text NOT NULL
);

ALTER TABLE ONLY industries_industries 
    ADD CONSTRAINT industries_industries_pkey PRIMARY KEY (industry_id, mapped_industry_id);

ALTER TABLE ONLY industries_industries
    ADD CONSTRAINT industries_industries_industry_id_fkey FOREIGN KEY (industry_id) REFERENCES industries(id);

ALTER TABLE ONLY industries_industries
    ADD CONSTRAINT industries_industries_mapped_industry_id_fkey FOREIGN KEY (mapped_industry_id) REFERENCES industries(id);

Upon running sqlboiler, I get the following output:

Error: 3:22: expected ')', found '(' (and 10 more errors)
failed to format template

  0 // G retrieves all the industry's .
  1 func (i *Industry) G(mods ...qm.QueryMod) Query {
  2     return i.(boil.GetDB(), mods...)
>>> }
  4
  5 //  retrieves all the industry's  with an executor.
  6 func (i *Industry) (exec boil.Executor, mods ...qm.QueryMod) Query {
  7     queryMods := []qm.QueryMod{
  8         qm.Select("\"a\".*"),

To generate this output, I ran the sql script above in its own schema, and then used:

sqlboiler -s test_schema postgres

[nullbio]

Hi @jseriff -- we've found the problem in the code, we're going to work on a fix for this later tonight. Thanks for letting us know.

[jseriff]

Great, thanks - I also created #46, which may be related, but I was seeing a different failure case.

[aarondl]

Hey @jseriff.

Did a lot of work around this issue today. Not fully resolved but we're a lot closer. Ran way deeper than expected :)
Hopefully have this resolved tomorrow.

One of the things that will change in the new code is that your table was erroneously being flagged as a join table, which in sqlboiler is defined by something with only the 2 fkeys and a composite primary key. The existence of the "relation" field in your join table means that it will be a separate entity and you'll have to manage it yourself. This isn't too consequential in the long run, just something to be aware of that will change with the upcoming release.

Have a good night!

[jseriff]

Thanks for the update. I think having it as a separate managed entity is the expected behavior here, so the relation field can be managed.

Thanks!

[aarondl]

Hey @jseriff. Sorry for the late update. Lots of thought went into fixing this. As well as there was some issues others raised yesterday we wanted to take care of immediately. The dev branch is now passing with test schemas that look like yours (they're now forever in CI too to avoid regression). The naming of the relationships can be a little pathological with this new patch, but it should actually work now.

I haven't been able to reproduce your other issue on the new code, maybe give it a whirl and let me know how it goes?

[aarondl]

Forgot to add, if the dev branch is working well for you, we'll probably release this as 2.0.2, but want to make sure your bugs are fixed first :)

[aarondl]

It may have been premature but we've released v2.0.2. Let us know how it works!

[jseriff]

I've gotten the latest version, but #46 is now failing in a very spectacular way (I now get over 100 copies of each relationship). The files are quite large (over 1M), so it is not completely clear what is going on.

I cannot confirm whether this issue is resolved, due to the nature of the current failures.

[aarondl]

@jseriff This sounds like more of a problem with the postgres driver seeing your database incorrectly. That could be because you're doing advanced usage of schemas or something that we haven't accounted for.

--debug outputs JSON that shows the structure of your schema as sqlboiler sees it, and I wonder if there aren't 100 copies of the relationships in the JSON. This can help diagnose where the problem is, whether it's in the templates or the driver. (Use jq to format it or something).

At this point I'd be very grateful if you could provide us with your database schema so we can debug it and make sure it works with everything you've got, rather than piecemeal fixing one bug to uncover another. I'd just like to get you up and running and the fastest method would be this if it's possible.

[jseriff]

Unfortunately, I cannot provide the DB schema. However, I did identify a fix (it was related to schemas), and have created a PR for it.

[aarondl]

Excellent! I'll review this tonight, merge and release. Thanks for the PR.

[jseriff]

With your changes the other day, I believe this is resolved.

Thanks!

[EmiPhil]

This is still possible to achieve:

create table owner (
    id int primary key
);

create table self_ref (
    id int,
    owner_id int,
    foreign key (owner_id) references owner (id),
    primary key (id, owner_id),

    parent_id int,
    foreign key (parent_id) references self_ref (id, owner_id)
);

leads to fun like

// SelfRefRels is where relationship names are stored.
var SelfRefRels = struct {
	Owner         string
	Owner         string
	Owner         string
	Owner         string
	Owner         string
	OwnerSelfRefs string
	OwnerSelfRefs string
	OwnerSelfRefs string
	OwnerSelfRefs string
}{
	Owner:         "Owner",
	Owner:         "Owner",
	Owner:         "Owner",
	Owner:         "Owner",
	Owner:         "Owner",
	OwnerSelfRefs: "OwnerSelfRefs",
	OwnerSelfRefs: "OwnerSelfRefs",
	OwnerSelfRefs: "OwnerSelfRefs",
	OwnerSelfRefs: "OwnerSelfRefs",
}