So I am trying to write a latest_edited_or_published_by_author/2 function that gets the n most recently updated stories or story drafts. I have a query for the stories and query for the story drafts that I concatenate with union.
def latest_edited_or_published_by_author(%User{} = user, amount \\ 8) do
story_query =
from s in Story,
join: u in assoc(s, :authors),
where: u.id == ^user.id,
select: %{id: s.id, title: s.title, draft?: false}
story_draft_query =
from s in StoryDraft,
join: u in assoc(s, :authors),
where: u.id == ^user.id,
select: %{id: s.id, title: s.title, draft?: true}
union_query =
from q in story_query,
union: ^story_draft_query,
order_by: fragment("updated_at ASC"),
limit: (^amount)
union_query
|> Repo.all()
end
Here is an example of what the function returns:
[
%{
draft?: true,
id: "...",
title: "Title"
},
%{
draft?: false,
id: "...",
title: "Title"
},
%{
draft?: false,
id: "...",
title: "Title"
},
%{
draft?: false,
id: "...",
title: "Title"
}
]
All good so far, but there is a catch. I also need to return a list of author data for each story/story draft. There is a many-to-many relation between authors and stories, as well as between authors and story drafts. In both cases a join table is used to establish the many-to-many relation.
Here is an example of what I need to get:
[
%{
draft?: true,
id: "...",
title: "Title",
authors: [%{id: ..., name: ...}, %{id: ..., name: ...}]
},
%{
draft?: false,
id: "...",
title: "Title",
authors: [%{id: ..., name: ...}, %{id: ..., name: ...}]
},
%{
draft?: false,
id: "...",
title: "Title",
authors: [%{id: ..., name: ...}, %{id: ..., name: ...}]
},
%{
draft?: false,
id: "...",
title: "Title",
authors: [%{id: ..., name: ...}, %{id: ..., name: ...}]
}
]
Questions:
- How do I add the author data?
- I am also interested to know whether you generally advice returning a list of structs from the database, or a list of maps (or some other non-struct data) with the data you’ll use. I am not sure I am foreseeing all the consequences of doing one over the other.
Failed attempts:
- Using fragment(“array_agg(?)”, …) in combination with joins to piece together the list of authors. Also tried json_agg.
- Returning structs from the union anyway, so I can use
Ecto.preload. Union, however, only returned one type of struct (either all Story or all StoryDraft).
Edit: The Story and StoryDraft schemas have many fields in common, but also necessarily have unique fields.
.
.
.
