Polymorphic relations

[tanner0101]

https://hashrocket.com/blog/posts/modeling-polymorphic-associations-in-a-relational-database

[sherwinzadeh]

Motivation:
Imagine a social app with posts, comments, images, etc. The user can "like" a post, a comment or an image. They can also "comment" on a post, another comment, etc.

Polymorphism has always been the best solution for this need. You would have a Like class and LikeableEntity base class that Post, Comment, etc. would inherit from. This allows you to be able to list all of a user's likes across multiple classes.

Solutions without polymorphism are always lacking in one way or another. For example, you can have the Like table have different fields for postId, commentId, etc. This would let you list all likes across different "classes" but that's a waste of storage and you need to add more fields as you add more like-able objects. Alternatively a Like model can have a generic objectId and a type field which would be 'post', 'comment', 'image', etc. but the disadvantage here is that objectId would not be a foreign key or indexable. Another alternative is to have separate tables for PostLike, CommentLike etc. and while you can have foreign keys, you would not be able to list all likes across different classes.

Postgres support polymorphism with the INHERITS keyword:
https://www.postgresql.org/docs/10/tutorial-inheritance.html

Other ORM's support this: https://laravel.com/docs/5.7/eloquent-relationships#polymorphic-relationships. It would be great for Fluent to support this too!!!

[tanner0101]

@sherwinzadeh do you know if other SQL DBs (MySQL, SQLite, Oracle, MariaDB, Cockroach, etc) support INHERITS or something similar?

If the majority of DBs Fluent works with support such a feature, then it makes sense to consider utilizing it as a solution for polymorphism. Otherwise, if this is Postgres only, then we will probably need to go for one of the other approaches. In that situation, we could still add support for INHERITS to PostgresKit though.

[lukeify]

@tanner0101 Polymorphism is still useful without specific DB support for INHERITS type clauses; Laravel embraces polymorphism for all its supported database drivers, for example.

[fwgreen]

I really hope this feature comes to Fluent! I've been asking for a variation of this, Single Table Inheritance, for some time. I assume that most ORMs, like those based on JPA, don't rely on any feature of the RDBMS itself for inheritance and instead use a discriminator column to differentiate types. That said, the language side looks complicated: Property wrappers don't work with protocols and Swift lacks abstract classes, so how would you create a super type?

[0xTim]

No progress on this I'm afraid. @gwynne I'm assuming this isn't really on our radar and something we should tag with help-wanted to see if we can get a community PR?

[gwynne]

@0xTim I don't think this is in scope for Fluent 4 at all at this point, and I question whether it's worthwhile to consider it for the future - even in Postgres, INHERITS doesn't make everything "just work"; last time I did polymorphic entities with Fluent, it ended up meaning a lot of extremely ugly CHECK constraints and nasty cheats like generated columns with no values. @calebkleveter was there, he can tell you! 😅

That being said, if someone in the community wants to delve into it and come up with something workable, I'm not so against it as to reject it out of hand 🙂.

[AnarchoSystems]

Just read this and I haven't read how others are doing it, but here's my first idea:

inheritance is a bit like owning a unique reference to the parent. So you want a 1:1 relationship, but with the additional requirement that the parent references exactly one child table of multiple possible child tables.

I think a lot of this could be achieved completely with checks on the Swift side.

• Have the parent reference each possible child table with @ChildTable.
• Whenever create is called with a Parent, make sure using reflection that exactly one child table is actually referenced. The referenced child must have all its nonnullable fields initialized.
• Whenever update is called with a Parent, make sure using reflection that exactly one child table is actually loaded. "Loaded" is an important keyword here, the child must actually come from the data base rather than having been attached arbitrarily. That means that the @ChildTable needs some way to store this information.
• Have the above checks done at runtime by a database middleware ParentCheckMiddleware<Parent> that is automatically added to the db when you declare in a migration that you do inheritance.

Whether you actually store the @ChildTables in the parent table is an implementation detail as long as you do things only on the swift side. If we want to support this feature even when non-swift applications can write into the db, one would have to store this information and enforce the behavior in the db somehow.

[AnarchoSystems]

2 minute google validates this idea to an extent:
https://stackoverflow.com/questions/1730665/how-to-emulate-tagged-union-in-a-database

[AnarchoSystems]

Update: I had only cross read the answers in this stackoverflow question because I didn't have much time, but one of the answers actually makes this whole thing quite simple:

1. the parent's primary key should be an id plus the type (integer or enum)
2. the child references this primary key, uses the parent's id as its own primary key and checks that the referenced parent's type is correct

[wdunicornpro]

Instead of a polymorphic foreign key, model inheritance backed by classic IsA hierarchy in the E-R world might be an easier solution.
Hibernate has something similar: https://www.baeldung.com/hibernate-inheritance
For the original Post Comment Likable example, we would want the Delta Table pattern or @Inheritance(strategy = InheritanceType.JOINED) from Hibernate.