Ecto: Include join schema field in many_to_many query

I have a many_to_many association with an explicit join_schema that references the same model on both ends (kind of like the classic Follower (is a user) <-> Followee (is a user) thing).

To stick with the example if I query for the followers of a user I want to include the time when the followed the user. This data obviously is located on the join schema (let's call is Subscription).

If would just want the followers I would do it like this:

followers = User
|> Repo.get!(user_id)
|> assoc(:followers)
|> Repo.all()

To have this working I would have defined this on the user:

many_to_many(
  :followers,
  MyApp.User,
  join_through: MyApp.Subscription,
  join_keys: [followee_id: :id, follower_id: :id]
)

So let's say there is a created_at field on the Subscription model. How would I query to get it?

Solution

Since you already have the Subscription schema, you can define a has_many:

schema "users" do
  ...
  has_many :follower_subscriptions, MyApp.Subscription, foreign_key: :followee_id
  has_many :followee_subscriptions, MyApp.Subscription, foreign_key: :follower_id
end

And then get the subscriptions using:

follower_subscriptions = User
|> Repo.get!(user_id)
|> Repo.preload(follower_subscriptions: :follower)
|> Repo.all()

follower_subscriptions will then be a list of Subscription, each containing created_at, followee, followee_id, and follower_id.

^{Code untested; let me know if there's a typo.}