Please help me write right query

Phoenix Forum Questions / Help
, ,
1

ok, I have users and projects tables which relations is has_many throw users_projects table.

also I have current_user, which is logged in.
I need to get All users with preloaded projects, but projects must be only common/general/shared(don’t know how to say it correctly) with current user.

for ex: user1: {projects: a, b, c} and user2: {projects: a,b, d}.
if user1 is logged in hi must see only user2: {projects: a,b}

here is my unsuccessful try:

project_ids = current_user
|> Repo.preload(:projects)
|> Map.get(:projects)
|> Enum.map(fn (p) -> Integer.to_string(p.id) end)

query = (
  from u in User,
  where: u.id != ^current_user.id,
  join: p in assoc(u, :projects), where: p.id in ^project_ids,
  select: %{
    user: %{
      id: u.id,
      name: u.name,
      email: u.email,
      projects: p
    }
  }
)
2

Why is it unsuccessful? Is there an error? If so, what does the error say?

3

in example user1: {projects: a, b, c} and user2: {projects: a, b, d}.
if i use my query in this situation I get this:

[{
  user_name: user1 //user values
  projects: a, //one project, but need [a,b]
 },
 {
  user_name: user1 //user values
  projects: b, //one project, but need [a,b]
 }
 ]

I need this:

[
name: user1
projects: [a, b]
]
4

Can you collect them afterwards?

With something like Enum.reduce or Enum.group_by?

Enum.group_by(
  results,
  fn %{user_name: user_name} -> user_name end,
  fn %{projects: projects} -> projects end
)

iex(2)> results = [%{
...(2)>   user_name: "user1",
...(2)>   projects: "a",
...(2)>  },
...(2)>  %{
...(2)>   user_name: "user1",
...(2)>   projects: "b"
...(2)>  }
...(2)> ]
[%{projects: "a", user_name: "user1"}, %{projects: "b", user_name: "user1"}]
iex(3)> Enum.group_by(results, fn %{user_name: user_name} -> user_name end, fn %{projects: projects} -> projects end)
%{"user1" => ["a", "b"]}
1 Like
5

I’ll try to do it, thanks

6

Together with the query

my_projects_subquery =
  Project
  |> join(:inner, [p], up in UserProject, p.id == up.project_id)
  |> where([p, up], up.user_id == ^current_user.id)
  |> select([p, up], p)

User
|> where([u], u.id != ^current_user.id)
|> join(:inner, [u], up in UserProject, u.id == up.user_id)
|> join(:inner, [u, up], p in Project, up.project_id == p.id)
|> join(:inner, [u, up, p], my_p in subquery(my_projects_subquery), my_p.id == p.id)
|> select([u, up, p, my_p], {u, p})
|> Repo.all()
|> Enum.group_by(
  fn {%User{id: user_id}, _project} -> user_id end,
  fn {_user, project} -> project end
)

Maybe it’s possible to replace Enum.group_by with some SQL …

1 Like
7

thanks thanks thanks !!! your save my university project )

8

If you only need the names of the projects, maybe you can use string_agg(expression, delimiter). Then you wouldn’t need the last Enum.group_by …

Not tested:

defmacrop string_agg(expression, partition_by) do
  quote do
    fragment("string_agg(?, ' ')", unquote(expression))
  end
end

my_projects_subquery =
  Project
  |> join(:inner, [p], up in UserProject, p.id == up.project_id)
  |> where([p, up], up.user_id == ^current_user.id)
  |> select([p, up], p)

User
|> where([u], u.id != ^current_user.id)
|> join(:inner, [u], up in UserProject, u.id == up.user_id)
|> join(:inner, [u, up], p in Project, up.project_id == p.id)
|> join(:inner, [u, up, p], my_p in subquery(my_projects_subquery), my_p.id == p.id)
|> select([u, up, p, my_p], %{user: u, projects: string_agg(p.name}) # assuming projects have a `name` column
|> Repo.all()