How to compose ecto queries with join

Question

I have three models User, Group, UserMembership.

Relationship among them is

defmodule Samajika.Group do
  has_many :user_memberships, MyApp.UserMembership
  has_many :users, through: [:user_memberships, :user]
end

UserMembership model has a type column that stores the type of membership. For eg., it might store owner, tenant etc.

Now I want to query Group and get its members who are only tenants.

Ecto.assoc(group, :users) |> Repo.all

The above gives me all users. The query looks like this

#Ecto.Query<from u0 in Samajika.User, join: u1 in Samajika.UserMembership,
 on: u1.group_id in ^[1], where: u0.id == u1.user_id, distinct: true>

I want to use Ecto query composition to add where clause with type on user_memberships table.

I have added this code in my group

def tenant(query) do
    from c in query, where: c.type == "tenant"
end

and I do this

Ecto.assoc(group, :users) |> Group.tenant

however this adds where clause on the users table, rather than join table user_memberships

What should I modify in my tenant function to add the condition on the join table?

Dogbert Dogbert · Accepted Answer · 2016-04-29T03:57:53

Pass a list to in in the from call:

def tenant(query) do
  from [_u, c] in query, where: c.type == "tenant"
end

The first argument would be the main table and second would be the first join.