Ecto how to filter a has many through realtionship

user_3290423 0
Viewed 351

I am learning how to compose queries using Ecto. I would like to filter results from a has_many through relationship, with one call to the database via Repo.all() if that is possible.

I assumed I could do this by composing this query using existing queries. e.g.

def users(organization_id) do
  Repo.get(Organization, organization_id)
   |> Ecto.assoc(:users)
end

def bookings(%{organization_id: organization_id}) do
  Repo.get(Organization, organization_id)
    |> Ecto.assoc(:bookings)
end

and then, reuse one of the above:

def bookings(%{organization_id: organization_id, user_id: user_id}) do
  Repo.get(Organization, organization_id)
    |> Ecto.assoc(:bookings)
    |> where([u], u.user_id == ^user_id)
end

But this does does not filter results by a given user_id. How can it be done? What am I missing?

Schema Info:

  • An Organization can have many Users.
  • A User can have many Bookings.
  • An Organization has many bookings through Users.
ecto phoenix-framework elixir
1 Answers

You are after Ecto.Query.subquery/2. Also, Repo.get/1 already executes the query against the database; in your case, you should build the query and only then execute it.

An Organization can have many Users

That said, there is no such field as user_id in organizations table. To be able to filter by users, one needs to have users in the query.

I believe, somewhat along these lines would work (it’s impossible to tell precisely without seeing real schemas.)

  users = 
    from o in Organization,
    join: u in User,
    on: u.organization_id == o.id,
    where u.id == ^user_id

  query =
    from u in subquery(users),
      join: b in Booking,
      on: b.user_id == u.id

  Repo.all(query)

Optionally, instead one might use Ecto.assoc/2 for the latter:

users
|> Repo.all()
|> Ecto.assoc(:bookings)
|> Repo.all()
user_20352620
Related