Is there a easier way to cast values while using Repo.load?

Questions & Help Questions
, ,
1

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?

2

Hi, for similar scenario I simply used embeds_many :entries, MovieEntry instead of entries ({:array, :map}. In this way you wouldn’t need Repo.load and load_movie_entries function altogether as each entry in entries would be “casted” to MovieEntry embedded_schema on Repo.all(). I’m not sure this resolves your issue though, but I’d rather model this using embedded_schema for MovieEntry.

I would check how Ecto handles casting string dates when MovieEnry embedded_schema would have both dates typed as Date or DateTime.

3

@ppiechota Hi! For some reason, I thought this wouldn’t work and didn’t even bother to try using an embed… The main reason was that MovieEntry is defined as a schema and not an embedded_schema. It turns out Ecto does not really care what struct you pass to embeds_many. Really, really interesting! This works like a charm. Thanks for reminding me how flexible Ecto really is.

Although it begs the question: If for some reason I could not use an embed, is there really another alternative?

4

Then I would probably try to resolve the issue you faced with casting string to naive datetime. I’m not sure but check if replacing Repo.load with Repo.embedded_load would help.

From docs:

To load data from non-database sources, use Ecto.embedded_load/3.