Idiomatic way to always query active users using Ecto.assoc/2?

Questions / Help
1

In my application a user can have several states, represented by an enum. For 90% of queries I only want users with a status of :active. However, it’s a pain to need to specify this constraint over and over again.

In most cases I am using assoc to do my joins in my query, likeso:

def get_accounts(user) do
  Repo.all(
    from a in Account,
      join: u in assoc(a, :user),
      where: u.id == ^user.id,
      where: u.status == "active"
   )
 end

I know I could create a composable query that always gets active users, but then I can’t use assoc/2.

defp active_user do
  from u in User, where: u.status == "active"
end

I was thinking of creating an additional schema that is ActiveUser which references my user table that I can use in assoc/2? But I’m not sure that’s the right way to go, or how it would only limit active users.

Any suggestions or do I just need to specify where: user.status == "active" in all my queries?

2

Something like this should work (untested)

def only_active(query) do
  from q in query, where: q.status == "active"
end

acc_query = from a in Account, join: u in assoc(a, :user), where: u.id == ^user.id

acc_query |> only_active() |> Repo.all()
3

I’m not sure this will work, but it’s worth a try and might give you some ideas on something that works for you:

defp accounts_query(user) do
  from a in Account,
    join: u in assoc(a, :user),
    where: u.id == ^user.id
end

defp accounts_query(user, :active) do
  from u in accounts_query(user),
    where: u.status == "active"
end

def get_accounts(user), do: user |> accounts_query() |> Repo.all()
def get_accounts(user, :active), do: user |> accounts_query(:active) |> Repo.all()
4

Yeah as I hint I never have anything write queries straight-out but rather wrap the queries up in functions. The default ‘:active’ state would just be a default argument, thus the way @ryh did it could be more succinctly like (in my usual style):

def query_accounts(opts \\ []), do: query_accounts(nil, opts)
def query_accounts(format, opts) do
  query = from a in Account

  query =
    case opts[:user] do
      nil -> query
      %{id: user_id} ->
        query = join(query, :left, [a], u in assoc(a, :user), u.id == ^user_id)

        case opts[:status] || :active do
          :all -> query
          status -> where(query, [a, u], u.status == ^status)
        end
    end

  query
end

Then you just use it like any other normal function that returns a query (which in my case often get’s composed with and combined with other queries, I still really really wish ecto had named joins, but those generally either get called by the repo or combined into transactions depending before this hit the primary API interface).