Polymorphic associations pattern

What is a polymorphic association?Copy Anchor Link

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.

Sample data:

comments

posts

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 relationshipCopy Anchor Link

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 modelsCopy Anchor Link

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);;
  }
}

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 relationshipCopy Anchor Link

Relationship comment_post {
  type: 'many_to_one'
  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 datasetCopy Anchor Link

Dataset community {
  data_source_name: 'your_datasource_name'

  models: [comments, posts]

  relationships: [
    relationship(comment_post, true)
  ]
}

Step 4: QueryCopy Anchor Link

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

AMQL puts the type condition in the ON clause:

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

Result:

• 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 typeCopy Anchor Link

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.

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

Key pointsCopy Anchor Link

• 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 AMQL 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.

Additional resourcesCopy Anchor Link

• Back to overview
• Composite keys pattern
• How joins work
• AML Relationship reference