Select users where timecard submitted

Question

Say I have some users:

| id | name  |
|----|-------|
| 1  | bob   |
| 2  | bill  |
| 3  | barry |

these users submit timecards each week:

| id | date         | user_id | week |
|----|--------------|---------|------|
| 1  | '2018-01-01' | 1       | 1    |
| 2  | '2018-01-02' | 1       | 1    |
| 3  | '2018-01-01' | 2       | 1    |

I now want to write an ecto query which returns a list of users who have submitted timecards for a given week.

I have tried the following:

def get_users_with_timecards(week) do
  import Ecto.Query, only: [from: 2]

  from(u in User,
    join: tc in Timecard,
    where: tc.week== ^week,
    distinct: u.id
  )
  |> Repo.all()
end

running the following, I get:

iex>get_users_with_timecards(1) |> length
3

I would like to return only the first 2 users - those who have submitted timecards.

Answer 1

I missed the on: part in from/2. The following works for me:

def get_users_with_timecards(week) do
  import Ecto.Query, only: [from: 2]

  from(u in User,
    join: tc in Timecard,
    on: u.id == tc.user_id,
    where: tc.week== ^week,
    distinct: u.id
  )
  |> Repo.all()
end