I need to convert this raw sql into ecto
select
talks.name, talks.id, talks.time_start, talks.is_women_only, talks.creator_id,
(
select json_agg(participant) from (
select timetables.user_id, users.fname, users.image_url
from timetables
inner join users on timetables.user_id = users.id
where timetables.talk_id = talks.id
union
select talks.creator_id as user_id, users.fname, users.image_url
from talks left join users on talks.creator_id = users.id
limit 5
) as participant
) as Panel,
count(timetables.id) filter(
where timetables.user_id is null
) as available_timetables
from talks inner join timetables on timetables.talk_id = talks.id
group by talks.id, talks.name
order by talks.time_start desc
I managed to convert it to this and it works. I’m just learning ecto and I know instead of using fragment in users, I can use subquery but I did try to but never get the same result using fragment. Can this fragment converted into subquery?
from(t in Talk,
# left_join: u in assoc(t, :creator),
inner_join: ts in assoc(t, :timetables),
on: ts.talk_id == t.id,
select: %{
id: t.id,
name: t.name,
time_start: t.time_start,
is_women_only: t.is_women_only,
creator_id: t.creator_id,
num_empty_timetables: count(ts.id) |> filter(is_nil(ts.user_id)),
users: fragment("select json_agg(participant) from (
select timetables.user_id, concat(users.fname,' ',users.lname) as name, users.image_url from timetables inner join users on timetables.user_id = users.id where timetables.talk_id = ?
union select talks.creator_id as user_id, concat(users.fname,' ',users.lname) as name, users.image_url from talks left join users on users.id = ? limit 5) as participant", t.id, t.creator_id)
},
group_by: [t.id, t.name],
order_by: [desc: t.time_start]
)
