Feature request: one-to-one relations the other way also (breaking change)

[benjie]

CREATE TABLE foo (
  id serial not null,
  note text
);

CREATE TABLE bar (
  foo_id integer not null primary key references foo,
  extended_note text
);

It would be nice in this case if foo had a barByFooId record rather than barsByFooId collection, since the bar.foo_id column is unique/primary key.

(It works fine if you model this the other way:

CREATE TABLE foo (
  id serial not null,
  bar_id integer references bar
  note text
);

CREATE TABLE bar (
  id serial not null,
  extended_note text
);

but this isn't always optimal)

[jmealo]

Any news on this?

[benjie]

Raising the priority of this because I've hit it again; and v4 would be the perfect time to introduce a breaking change for the better.

[chadfurman]

barsByFooId will work, though, right? It just returns a collection with only one element?

And is it a breaking change because it introduces a new function name?

[benjie]

It's a breaking change because if we detect the relation to be one-to-one we'd be removing the false one-to-many relationship that's currently present and using the one-to-one instead.

I think we can detect it quite easily: if the reference is on a primary or unique key then it's one-to-one.

We might be able to add some command-line flags to retain the previous behaviour, but that adds more work.

[benjie]

"primary key references" relations

[benjie]

Note to self: add an option people can use to gain backwards compatibility.

[hmaurer]

I just want to emphasise that it would be nice to have it on "UNIQUE" references as well as foreign keys (I need it for one of my projects). Looking forward to see this feature land!

[chadfurman]

Does this mean that if we have a join table, it will register as a one-to-one relationship because the foreign key is a primary or unique key?

[benjie]

No, this is only for a single column primary key (or other unique key); I think there’s another issue for many-to-many relations.

[benjie]

Added in graphile/graphile-engine#177

[shellscape]

@benjie we've got legacyRelations: 'omit' in our options as a standard but still seeing some FooConnection on one-to-one relationships where a table has a foreign key on another table's primary key. Is there an additional trick to getting v4 to behave in one-to-one?

Edit: OK this was a one-to-one with a view, and required a @primaryKey smart tag on the view. Hopefully that's useful for future people.