Counting associated devices and jobs with user gives wrong results

Questions / Help
, ,
1

Hi, I am trying to count associated devices and jobs with users. I have the following query. It gives the wrong output. I have no idea why. Please help. Generated SQL is also giving the wrong results, I have checked by running it on the database directly. Seems, something wrong with my ecto query.


 def get_user_info(conn, _params) do

    query = from u in User,
            join: d in Device,
            where: d.id == u.id,
            join: j in Job,
            where: j.id == u.id,
            group_by: u.id,
            select: %{id: u.id, name: u.name, email: u.email, username: u.username, devices_count: count(d.id), jobs_count: count(j.id) }

    data = query |> Repo.all() |> Enum.map(fn(user) -> struct(User, user) end)

    render( conn, "info.html", data: data )
  end

Generated Query:

u0."id", u0."name", u0."email", u0."username", count(d1."id"), count(j2."id") FROM "users" AS u0 INNER JOIN "devices" AS d1 ON TRUE INNER JOIN "jobs" AS j2 ON TRUE WHERE (d1."id" = u0."id") AND (j2."id" = u0."id") GROUP BY u0."id"
2

I suspect this is not the SQL you want

Should the where:s here be on: instead?

3

I have tried with :on but gives same result…so moved to where without improvement. I made small change thats more logical and on: instead of where: but still gives weird counts.

def get_user_info(conn, _params) do

    query = from u in User,
            join: d in Device, on: d.user_id == u.id,
            join: j in Job, on: j.user_id == u.id,
            group_by: u.id,
            select: %{id: u.id, name: u.name, email: u.email, username: u.username, devices_count: count(d.id), jobs_count: count(j.id) }

    data = query |> Repo.all() |> Enum.map(fn(user) -> struct(User, user) end)

    render( conn, "info.html", data: data )
  end
4

Solved: Needed to use distinct fragments as I have figured out that duplicate data had been retrieved with two join. If anyone has any improvement suggestions can still suggest.

def get_user_info(conn, _params) do

    query = from u in User,
            join: d in Device, on: d.user_id == u.id, distinct: true,
            join: j in Job, on: j.user_id == u.id,
            group_by: u.id,
            select: %{id: u.id, name: u.name, email: u.email, username: u.username, devices_count: count(fragment("DISTINCT ?", d.id)), jobs_count: count(fragment("DISTINCT ?", j.id))}

    data = query |> Repo.all() |> Enum.map(fn(user) -> struct(User, user) end)

    Logger.info("ResultTest : #{inspect(data)}")

    render( conn, "info.html", data: data )
  end