Ecto - multiple where conditions not working. Elixir

Question

I am using Ecto to get data from the database.

there is a camera table. and camera shares table. I am trying to get all the cameras where camera's owner_id is equal to the given owner_id.

CameraShare table has camera_id and user_id which are in relation with owner and camera.

I am trying to get all cameras where

camera's owner_id = given id

All those cameras as well where.

CameraShare's user_id = given id
CameraShare's camera_id = Camera's id

when I do the above query, I only get those cameras where CameraShare's user_id and camera_id are same with given_id and camera's own id.

Camera
|> join(:left, [u], cs in CameraShare)
|> where([cam, cs], cs.user_id == ^given_id)
|> where([cam, cs], cam.id == cs.camera_id)
|> preload(:owner)
|> Repo.all

How I can get all the cameras All Cameras is equal to the ones where the camera

|> where([cam], cam.owner_id == ^id)

as well as the above conditions also get fulfilled. with above already written Ecto query , I can get only the shared ones. not the owned ones.

I have tried this as well. But it doesn't work

  Camera
  |> join(:left, [u], cs in CameraShare)
  |> where([cam, cs], cs.user_id in ^account)
  |> where([cam, cs], cam.id == cs.camera_id)
  |> where([cam], cam.owner_id in ^account)
  |> where([cam], like(fragment("lower(?)", cam.name), ^("%#{String.downcase(search)}%")))
  |> preload(:owner)
  |> Evercam.Repo.all

Update: What I did to solve this is run 2 queries.

owned_cameras =
  Camera
  |> where([cam], cam.owner_id in ^account)
  |> where([cam], like(fragment("lower(?)", cam.name), ^("%#{String.downcase(search)}%")))
  |> preload(:owner)
  |> Evercam.Repo.all

shared_cameras =
  Camera
  |> join(:left, [u], cs in CameraShare)
  |> where([cam, cs], cs.user_id in ^account)
  |> where([cam, cs], cam.id == cs.camera_id)
  |> where([cam], like(fragment("lower(?)", cam.name), ^("%#{String.downcase(search)}%")))
  |> preload(:owner)
  |> Evercam.Repo.all

and then join them with ++ , But still looking for one query.

Aleksei Matiushkin Aleksei Matiushkin · Accepted Answer · 2019-08-01T13:41:38

There is a sledgehammer variant with Ecto.Query.union/2, but it seems it might be done better with Ecto.Query.or_where/3. Somewhat like (I obviously cannot test this code, but it should work):

  Camera
  |> join(:left, [u], cs in CameraShare)
  |> where([cam], like(fragment("lower(?)", cam.name), ^"%STR%")))
  |> where([cam], cam.owner_id in ^account)
  |> or_where([cam], cs.user_id in ^account and cam.id == cs.camera_id)
  |> preload(:owner)
  |> Evercam.Repo.all

Also, LIKE in most RDBMS is case-insensitive by default (depending on collation used.)

Ecto - multiple where conditions not working. Elixir

1 Answers