Having problems filtering Ecto models

Phoenix Forum Questions / Help
,
#1

Hello. I’ve been having a hard time figuring out how to do this query, and me not having a lot of experience with SQL doesn’t help at all.

I have three models/schema: Screen, EventMedia, and Media.
A Screen has many Medias through EventMedia.
An EventMedia belongs to three models: Screen, Event, and Media.
Media has a type field which can contain only two values "multimedia" or "document".
A Screen can have :medias of both types

What I have to do is to get only Screens that have no Media of type : multimedia. Any ideas how to do this?

I’m able to do the opposite of this query (Screens that have Media of type:multimedia) using this query:

Screen
|> join(:left, [q], em in EventMedia, on: em.screen_id == q.id)
|> join(:left, [_q, em], m in Meda, on: em.media_id == m.id)
|> where([_q, em, m], not is_nil(em.id) and m.type == "multimedia")
|> distinct([q, _cm, _m], q.id)

I’ve tried this query:

Screen
|> join(:left, [q], em in EventMedia, on: em.screen_id == q.id)
|> join(:left, [_q, em], m in Meda, on: em.media_id == m.id)
|> where([_q, em, m], is_nil(em.id))
|> distinct([q, _cm, _m], q.id)

However it also filters out Screens that only have :medias of type: document, which isn’t right. I’ve also tried other queries but I can’t get it right. Any ideas how to do this? Thanks!

#2

Doesn’t != work?

Screen
|> join(:left, [q], em in EventMedia, on: em.screen_id == q.id)
|> join(:left, [_q, em], m in Meda, on: em.media_id == m.id)
|> where([_q, em, m], not is_nil(em.id) and m.type != "multimedia")
|> distinct([q, _cm, _m], q.id)
#3

I think this will include Screens that have both types of Media and this also won’t include Screens that have don’t have any Media yet.

#4

Correct, I focused my reply only on filter. First of all you should read about SQL joins:

A proper join type here is inner.

schemas 
defmodule TodoList do
  use Ecto.Schema

  schema "todo_lists" do
    field(:title)
    has_many(:todo_list_items, TodoListItem)
    has_many(:todo_items, through: [:todo_list_items, :todo_item])
    timestamps()
  end
end

defmodule TodoListItem do
  use Ecto.Schema

  schema "todo_list_items" do
    belongs_to(:todo_list, TodoList)
    belongs_to(:todo_item, TodoItem)
    timestamps()
  end
end

defmodule TodoItem do
  use Ecto.Schema

  schema "todo_items" do
    field(:description)
    field(:type)
    timestamps()
  end
end
2 inner joins 
defmodule Example do
  alias Ecto.Query
  require Query

  def sample do
    TodoList
    |> Query.from(as: :todo_list)
    |> Query.join(:inner, [todo_list: todo_list], assoc(todo_list, :todo_list_items),
      as: :todo_list_item
    )
    |> Query.join(:inner, [todo_list_item: todo_list_item], assoc(todo_list_item, :todo_item),
      as: :todo_item
    )
    |> Query.where([todo_item: todo_item], todo_item.type != "hobby")
  end
end

iex> Example.sample()
#Ecto.Query<from t0 in TodoList, as: :todo_list,
 join: t1 in assoc(t0, :todo_list_items), as: :todo_list_item,
 join: t2 in assoc(t1, :todo_item), as: :todo_item, where: t2.type != "hobby">
shorter with 1 ecto's inner join using through 
defmodule Example do
  alias Ecto.Query
  require Query

  def sample do
    TodoList
    |> Query.from(as: :todo_list)
    |> Query.join(:inner, [todo_list: todo_list], assoc(todo_list, :todo_items), as: :todo_item)
    |> Query.where([todo_item: todo_item], todo_item.type != "hobby")
  end
end

iex> Example.sample()
#Ecto.Query<from t0 in TodoList, as: :todo_list,
 join: t1 in assoc(t0, :todo_items), as: :todo_item, where: t1.type != "hobby">
1 Like