Multiple Polymorphic Associations in Ecto

Hi all,

Finally think I have enough of a grasp on Elixir and Ecto to formulate this question.

Basically, I’m looking to implement the same functionality that the Public Activity gem brings to a Rails app, tracking user activity.

Using an example of comments, let’s say users can comment on other users and on some other models, say cars and boats.

In Rails you’d have:

* commentable_type
* commentable_id

the Ecto docs point out:

The problem with this approach is that it breaks references in the database. You can’t use foreign keys and it is very inefficient, both in terms of query time and storage.

From what I can tell based on comments on some of the original ecto issues and various SO questions, Ecto would encourage two options which Bill Karwin terms:

  • Exclusive Arcs: Create multiple foreign key columns, each referencing one parent. Enforce that exactly one of these foreign keys can be non-NULL.
  • Concrete Supertable: Instead of the implicit “commentable” superclass, create a real table that each of your parent tables references. Then link your Comments to that supertable.

This makes sense for the comments example above, and I can happily have user_comments, boat_comments, and car_comments :thumbsup:

In contrast, the case of tracking activity seems much more unwieldy, as can be seen by the model used by Public Activity (code paraphrased)

# Define resource being tracked
belongs_to :trackable, :polymorphic => true
...
# Define ownership to a resource responsible for this activity
belongs_to :owner, :polymorphic => true
# Define ownership to a resource targeted by this activity
belongs_to :recipient, :polymorphic => true

I couldn’t think through a way to transfer that sort of polymorphism to the patterns in the Ecto docs:

Another aspect was trying to think through the end goal. Ultimately, it seems like the what I’m trying to accomplish is “log” the changes made to the database by users. In a sense storing store metadata about the database itself (or past states) in the database.

Any suggestions on how to accomplish this? Or alternatives methods of tracking how the database has changed?

Thanks!

Sources:
Ecto - Support polymorphic belongs to #389
SO - Why can you not have a foreign key in a polymorphic association?
Ecto - Ecto.Schema Docs

3 Likes

Ive been trying to tackle this one lately and I’m kinda stumped how this would be at all possible with ecto’s ‘abstract tables’. Especially when you have 2 ‘polymorphic’ owners then I don’t think you can even attempt this pattern since it would attempt to make 2 tables for the double abstract relationship?

So how can we go about replicating this? I don’t see too many clean methods besides adding a type column. You could do has many through tables for each relationship type but I see that getting pretty crazy for something like activity.

Let me know if you made any progress on this. Would love to chat.

1 Like

I first missed this discussion but I would also include the third option listed in the SO answer: use a many to many relationship (even though you may have a single entry). For example, you would have a users_activity_owners, users_activity_recipients and so on. Ecto 2.0 makes this approach straight-forward. This comes with the benefit, in this particular case, that if you have multiple recipients, you get support for them for free now. Ecto 2.0 also makes it easy to work with many-to-many without having to create intermediate modules (only the tables).

You can try to mimic polymorphic belongs to relationship by hand in Ecto if you really want to. But that’s asking for troubles in the long run.

1 Like

Added this third approach to the docs: https://github.com/elixir-ecto/ecto/commit/178a912777c0c78f2126ee05f0c290401f9f3591

5 Likes

I like the many_to_many approach, its very reasonable.

But what it I want to query the relationship from the “Children Site”. Like when I want to access the parent of a Comment, where the parent could be a Post or a Task.

Should I add a column in comment, but than the whole approach would be meaningless.

I think I’d rather go with different foreign keys in comment, that could be empty, right?

@josevalim :slight_smile:

3 Likes

@Valentin-Seehausen I think everything you’d be able to do with your approach, you’d also be able to do it with a join table like @josevalim suggests, except that the later is a much cleaner approach (there’s never a dangling entry, let it be a join record or a foreign key field set to null): at any time, your database schema is consistent and all relationships can be verified without exceptions.

Are there use cases that you weren’t able to model with that later approach? The one you’re mentioning is solved by a simple join, allowing you to query whatever you want in both directions.

I’m myself coming from the Rails ecosystem, and having to reference relationships using a commentable_type / commentable_id made me cringe every time, and even if it perfectly works from a functional perspective, it’s often not the best if you’re looking at the schema itself, as it circumvents any native database foreign key option.

1 Like