Polymorphic associations pattern

What is a polymorphic association?

A polymorphic association is a foreign key that can point into more than one table. A second column says which table.

For example, users can comment on posts, photos and videos. The comments table stores commentable_type (Post, Photo or Video) and commentable_id. The id only means something together with the type: comment c3 points to photo x1, not post x1.

Polymorphic association from comments to posts, photos and videos

[HTML node] — comments: highlighted columns take part in the relationship
[HTML node] — posts: highlighted columns take part in the relationship
[HTML node] — photos: highlighted columns take part in the relationship
[HTML node] — videos: highlighted columns take part in the relationship

comments → posts (many_to_one where commentable_type = 'Post')
comments → photos (many_to_one where commentable_type = 'Photo')
comments → videos (many_to_one where commentable_type = 'Video')

Sample data:

comments

id	commentable_type	commentable_id	rating
c1	Post	x1	4
c2	Post	x2	5
c3	Photo	x1	3
c4	Photo	x9	2
c5	Video	x1	1

posts

id	title
x1	Hello
x2	World

The question: what is the total comment rating per post, without losing photo and video comments from the total?

Solution: filter one side inside the relationship

Declare a many_to_one relationship on the id, and add a where {} block that restricts the from side to the matching type. The filter becomes part of the join condition, not a report filter, so no comment row is removed.

Step 1: Define models

comments.model.aml
Model comments {
  type: 'table'
  table_name: 'app.comments'
  data_source_name: 'your_datasource_name'

  dimension id {
    type: 'text'
    primary_key: true
  }

  dimension commentable_type { type: 'text' }
  dimension commentable_id { type: 'text' }
  dimension rating { type: 'number' }

  measure total_rating {
    label: 'Total Rating'
    type: 'number'
    definition: @aql sum(comments.rating);;
  }
}

posts.model.aml
Model posts {
  type: 'table'
  table_name: 'app.posts'
  data_source_name: 'your_datasource_name'

  dimension id {
    type: 'text'
    primary_key: true
  }

  dimension title { type: 'text' }
}

Step 2: Define the relationship

comment_post.relationship.aml
Relationship comment_post {
  type: 'many_to_one'
  on { from: r(comments.commentable_id) to: r(posts.id) }
  where { from: @aql
    // Restrict comments to only those belonging to Posts
    comments.commentable_type == 'Post'
  ;; }
}

where { from: } holds a condition on the from model only. The cardinality does not change: the relationship still says each comment matches at most one post.

Step 3: Add it to a dataset

community.dataset.aml
Dataset community {
  data_source_name: 'your_datasource_name'

  models: [comments, posts]

  relationships: [
    relationship(comment_post, true)
  ]
}

Step 4: Query

explore {
  dimensions { post: posts.title }
  measures { rating: sum(comments.rating) }
}

Holistics puts the type condition in the ON clause:

LEFT JOIN posts
  ON  comments.commentable_id   = posts.id
  AND comments.commentable_type = 'Post'

Result:

post	rating
Hello	4
World	5
NULL	6
total	15

Only real post comments match a post. c3 (Photo) and c5 (Video) share the id x1 with the post "Hello" but do not join to it.
The photo and video comments stay in the result under NULL, so the total matches the sum of comments.rating.

Joining every target type

Give each target its own relationship with its own where {} block. A single query can then report comments against posts, photos and videos together, and every comment lands on its own target.

comment_photo.relationship.aml
Relationship comment_photo {
  type: 'many_to_one'
  on { from: r(comments.commentable_id) to: r(photos.id) }
  where { from: @aql comments.commentable_type == 'Photo' ;; }
}

Key points

A where block references one model only. where { from: } may use columns of the from model, where { to: } columns of the to model. A condition that compares both models is a different relationship type, and Holistics rejects it here.
The filter lives on the relationship, not the model. Other relationships into the same model still see every row.
Soft deletes use the same block on the to side. To ignore deleted products, add where { to: @aql products.deleted_at is null ;; }. Facts pointing at a deleted product report under NULL instead of disappearing.

What is a polymorphic association?​

Solution: filter one side inside the relationship​

Step 1: Define models​

Step 2: Define the relationship​

Step 3: Add it to a dataset​

Step 4: Query​

Joining every target type​

Key points​

Additional resources​