Recursive Query (nested |> select statement)

Hello everyone,
I’ve been trying to create this query which returns a nested map %User{ playlists: [%Playlist{videos: [%Video{}, ...]}, ...], however i’m having some trouble getting it to work, specifically added a “subscribed” value to each of the Playlist objects. The problem is that I’m unable to update the playlist objects in the |> select statement. If I was returning a list of playlists, i could easily do this via:

Playlist
|> select([p, ps], %{p | subscribed: count(ps.id) > 0})

however, since i’m starting with User (User |> … |> …), i’m unable to do this. If anyone knows if this is possible, that would be great to know, thanks guys!

--- schema ---
User:  
  has_many :playlists, Playlist
  many_to_many :subscriptions, Playlist, join_through: "playlist_subscriptions"

Playlist:
  belongs_to :user, User
  many_to_many :subscribers, User, join_through: "playlist_subscriptions"

  has_many :videos, Video

M2M:
  belongs_to :user, User
  belongs_to :playlist, Playlist

--- input ---

User.get_subscribed_playlists(user_id, current_user_id)

--- query ---

def query(user_id, current_user_id) do
  User
  |> where([u], u.id == ^user_id)
  |> join(:left, [u], s in assoc(u, :subscriptions), on: [user_id: ^current_user_id])
  |> join(:left, [u, s], p in assoc(u, :playlists))
  |> join(:left, [u, p, s], v in assoc(p, :videos), on: [playlist_id: p.id])
  |> select([u, s, p, v], %{p | subscribed: count(s.id) > 0})
  |> group_by([u, s, p, v], [u.id, s.id, p.id, v.id])
  |> preload([_user, subscriptions, playlists, videos], [playlists: {playlists, videos: videos}])
end

--- desired output ---

%User: {
  playlists: [
    %Playlist{
      videos: [
        %Video{},
        %Video{},
        ...
      ]
    },
    ...
  ]
}

Does anyone know if this is actually possible? Not worth spending all the time trying to make it work if it can’t even be done

Try to move preload instructions from query to repo. Using Ecto.Repo you’re able to pass a query which will be used for preloading, so you could do something like this:

def playlists_with_subscribed_field do
  Playlist |> join(...) |> select(...) |> distinct(true)
end
...
user |> Repo.preload(playlists: {playlists_with_subscribed_field(), [:videos]})
2 Likes