Hi! I have the following situation to illustrate my question:
A table with a bunch of information in it and a view that groups this table information based on the values of a single column (keywords
). The view has two columns: name
(the key) and entries
(the values) - which are a jsonb_agg
of the grouped table values:
select
distinct unnest(keywords) as keyword,
jsonb_agg(m) as entries
from movie_entries me group by keyword;
Movie Entries
id | keywords | inserted_at |
---|---|---|
1 | {horror, action} | 2021-03-12 00:00:00 |
2 | {comedy, romantic} | 2021-03-12 00:00:00 |
3 | {adventure, thriller} | 2021-03-12 00:00:00 |
Movies Genre Entries (Materialized View)
name | entries |
---|---|
horror | jsonb list of movies |
action | jsonb list of movies |
comedy | jsonb list of movies |
romantic | jsonb list of movies |
adventure | jsonb list of movies |
thriller | jsonb list of movies |
This view is represented using a schema (MovieGenreEntry
), which has two fields name
(string
) and entries
({:array, :map}
). Then, it’s possible to load a MovieGenreEntry
with its “related” MovieEntry
entries like this:
MovieGenreEntry
|> Repo.all()
|> Enum.map(&MovieGenreEntry.load_movie_entries/1)
def load_movie_entries(%MovieGenreEntry{entries: entries} = entry) do
%{entry | entries: Enum.map(entries, &load/1)}
end
defp load(data) do
data = Map.drop(data, ["inserted_at", "updated_at"])
Repo.load(MovieEntry, data)
end
I’m currently dropping both “inserted_at” and “updated_at” fields from the map because Repo.load/2
throws an error trying to cast the string dates to a :naivedatetime
type (which makes sense):
** (ArgumentError) cannot load `"2021-03-12 00:00:00"` as type :naive_datetime for field `inserted_at` in schema MovieEntry
So I was wondering… Is there an easier way to achieve this?