Recursive Query (nested |> select statement)

Questions / Help
1

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{},
        ...
      ]
    },
    ...
  ]
}
2

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

3

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