Order many to many relationship by their pivot table's columns (inserted_at)

Questions / Help
1

Hey good people! I want to order a many to many relationship between a Playlist and a Track by the inserted_at column of the pivot table playlist_track in ascending order:

q = from p in Playlist,
    join: t in assoc(p, :tracks),
    join: pt in "playlist_track", on: p.id == pt.playlist_id, on: t.id == pt.track_id,
    preload: [tracks: [:artist]],
    order_by: [asc: pt.inserted_at],
    limit: 1

This is what I got. But it’s not working. Can you help? Thanks already!

2

The order_by used in your query refers to the Playlist schema and not the tracks inside the playlist, just like the limit refers to the Playlist as well.

You might want to customize the preload query, like:

tracks_query = from t in Track,
  join: pt in "playlist_track", on: t.id == pt.track_id,
  preload: [:artist],
  order_by: [asc: pt.inserted_at]

q = from p in Playlist,
  join: t in assoc(p, :tracks),
  preload: [tracks: ^tracks_query]

didn’t test this, but it should be close enough to what you need.

3

@aesmail a lot of thanks! I will try it. Again thank you!