Ecto subqueries

Questions / Help
1

I’m trying to run this SQL query using ecto

SELECT
	*
FROM
	projects
WHERE
	id in(
		SELECT
			project_id FROM project_user
		WHERE
			user_id = '88873349-be56-4434-bcd3-3235bc148a34')

I tried using Fragments to accomplish this:

def list_user_projects(%User{} = current_user) do
    projects =
      Repo.all(
        from p in Project,
          where:
            p.id in fragment(
              "select project_id from project_user where user_id = ?",
              ^current_user.id
            )
      )
      |> Repo.preload(:users)

    {:ok, projects}
  end

But when I execute it I get this error:

Postgrex expected a binary of 16 bytes, got "88873349-be56-4434-bcd3-3235bc148a34". Please make sure the value you are passing matches the definition in your table or in your query or convert the value accordingly.

Seems like the current_user.id variable isn’t being converted correctly. I see there’s a Ecto.QubQuery module but the docs are currently empty.

Update

I found the Ecto function to transform a string to a binary uuid. I was able to update my function to get it to work.

def list_user_projects(%User{} = current_user) do
    {:ok, user_uuid_id} = Ecto.UUID.dump(current_user.id)

    projects =
      Repo.all(
        from p in Project,
          where:
            p.id in fragment(
              "select project_id from project_user where user_id = ?",
              ^user_uuid_id
            )
      )
      |> Repo.preload(:users)

    {:ok, projects}
  end

Though this works is there a cleaner way to accomplish it without fragments? Fragments seem like a last result for “complex” queries but this is a pretty basic query.

2

You can also use Ecto.Query.API.type/2 directly in query instead of Ecto.UUID.dump/1.

Do you have declared association between User and Project?
if you have the following line in User module

many_to_many :projects, Project, join_through: "project_user"

then you can write a function as

current_user
|> Ecto.assoc(:projects)
|> Repo.all()
|> Repo.preload(:users)

or

current_user
|> Repo.preload(projects: :users)
|> Map.get(:projects)

And solution without assoc:

query =
   from projects in Project,
    inner_join: project_user in "project_user",
    on: projects.id == project_user.project_id and project_user.user_id == ^current_user.id

query
|> Repo.all()
|> Repo.preload(:users)