CODEWITHSUNDEEP
elixirecto
Junaid Farooq
Junaid Farooq

Reputation: 2598

Ecto - multiple where conditions not working. Elixir

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

All those cameras as well where.

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.

Upvotes: 3

Views: 4251

Answers (1)

Aleksei Matiushkin
Aleksei Matiushkin

Reputation: 120990

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.)

Upvotes: 1

Related Questions