# Polymorphic associations pattern

> Learn how to join a model whose foreign key points into different tables depending on a type column

## 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`.

<ReactFlowDiagram id="erd-polymorphic-associations" height={360} fixed />

**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

```aml title="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);;
  }
}
```

```aml title="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

```aml title="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

```aml title="community.dataset.aml"
Dataset community {
  data_source_name: 'your_datasource_name'

  models: [comments, posts]

  relationships: [
    relationship(comment_post, true)
  ]
}
```

### Step 4: Query

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

Holistics puts the type condition in the `ON` clause:

```sql
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.

```aml title="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.

## Additional resources

- [Back to overview](/docs/modeling-patterns)
- [Composite keys pattern](/docs/modeling/modeling-patterns/composite-keys)
- [Relationships overview](/docs/relationships)
- [AML Relationship reference](/reference/aml/relationship)