Hello,

I would like to hear your opinion on writing controller/context functions with DB queries.

Let’s say that we have an hypothetical app where people can create projects. We have a show project page but we also have a page where all projects are listed, then in the user area, we have places where we want to list only user projects or a place with only active projects, or 5 latest projects etc. What I mean, on different places inside our app, we need to fetch different projects based on some conditions. How do you approach writing queries in this situation?

Do you write a different function for each of those?

def get_user_projects(user) do
  query = from p in Project, where: p.user_id == ^user.id
  Repo.all(query)
end

def get_user_active_projects(user) do
  query = from p in Project, where: p.user_id == ^user.id and p.status == "active"
  Repo.all(query)
end

def get_user_latest_projects(user) do
  query = from p in Project, where: p.user_id == ^user.id, limit: 5
  Repo.all(query)
end

Or do you create more generic functions which creates a query based on opts

def list_user_campaigns(user, opts \\ []) do
    status = Keyword.get(opts, :status)
    limit = Keyword.get(opts, :limit)

    Campaign
    |> where(user_id: ^user.id)
    |> maybe_add_query_status(status)
    |> maybe_add_query_limit(limit)
    |> Repo.all()
end

defp maybe_add_query_status(query, nil), do: query
defp maybe_add_query_status(query, status), do: where(status: ^status)

defp maybe_add_query_limit(query, nil), do: query
defp maybe_add_query_limit(query, limit), do: limit(query, ^limit)

Or do you maybe take some completely different approach?