Dynamic clause being applied to unexpected schema(ecto 3.0.9)

ecto
ecto-query
#1

language_id is an optional parameter which could be nil. So I thought to have it dynamically applied to MediaItem

    ...
    conditions = true
    conditions =
      if language_id do
        dynamic([mi], mi.language_id == ^language_id and ^conditions)
      else
        conditions
      end

    query = from pl in Playlist,
    join: mi in MediaItem,
    where: pl.uuid == ^pid_uuid,
    where: mi.playlist_id == pl.id,
    where: ^conditions,
    order_by: [{^direction, field(mi, ^sorting)}],
    select:
    ...

the query works fine if language_id is nil but when language_id is a string like β€œen” I see a runtime error because the dynamic query is being applied to Playlist, which does not have a language_id column. However, the dynamic query I specified is on mi or MediaItem. Why is ecto trying to apply the query to Playlist(p0.language_id)?

here is the runtime error:

    ** (Ecto.QueryError) apps/word_api/lib/word_api/controllers/api/v1.3/v13.ex:329: field `language_id` in `where` does not exist in schema DB.Schema.Playlist in query:

from p0 in DB.Schema.Playlist,
  join: m1 in DB.Schema.MediaItem,
  on: true,
  where: p0.uuid == ^<<148, 214, 253, 35, 173, 6, 66, 11, 144, 130, 183, 111, 244, 179, 47, 89>>,
  where: m1.playlist_id == p0.id,
  where: p0.language_id == ^"en" and ^true,
  select: count("*")
#2

I believe this should work:
dynamic([pl, mi], mi.language_id == ^language_id and ^conditions)

Since MediaItem is being joined to Playlist, the first element in the list would be MediaItem and the second would be the joined table, Playlist.

You could also try using named joins, that way it is more clear which table you are referring to.

This post does a nice job of introducing them:

3 Likes
#3

Thanks for the help and link. Named joins make it a lot more readable for sure.

2 Likes