Ecto - querying a table based on an association

Question

I have the following tables:

schema "project_users" do
  belongs_to(:user, MyApp.User)
end

and

schema "users" do
  field(:search_vector, :tsvector)
end

I want to query project_users based on the search_vector result after querying users with which is has a belongs_to relationship.

I have the following query for users. I wonder how I can relate it to project_users:

def query_all_users(params \\ %{}, preload \\ []) do
  query = from(u in User, preload: ^preload)

  query_by(query, params)
end

defp query_by(query, %{"keyword" => keyword} = params) do
  query = from(q in query, where: fragment("search_vector @@ plainto_tsquery(?)", ^keyword))

  query_by(query, Map.delete(params, "keyword"))
end

Answer 1

I would recommend making a query that does the join and then filters as so:

defmodule ProjectUser do
  use Ecto.Schema

  import Ecto.Query

  schema "project_users" do
    belongs_to :user, User
  end

  @spec by_search_vector(Ecto.Queryable.t, String.t) :: Ecto.Query.t
  by_search_vector(query, search_vector) do
    from pu in query,
      join: u in assoc(pu, :user),
      where: fragment("? @@ plainto_tsquery(?)", u.search_vector, ^search_vector)
  end
end

This should easily scope project users down.