Best practices when writing context functions with DB queries

Questions / Help
, ,
1

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?

1 Like
2

I’ve seen both and both are fine. Go for what feels better for you.

6 Likes
3

I find that if I’m accessing the same basic data with some frequency, but using a lot of different flavors of essentially the same query, I’ll use your second, “generic query” approach. I do this because, for me, it’s easier to mentally deal with a single function with options that I’m using frequently than it is to try to remember many specific function names with few options; indeed I might have trouble remembering if I’ve covered a specific use case previously at all. I also find that the more generic approach is just more flexible because I may be able to mix/match options in ways I didn’t originally intend (this usually works out OK, there can be downsides). If I forget the options, finding my documentation about what’s available and what to worry about is also easier with a generic function/many options.

Now I do use the more specific function method, too, if I know that my calling use cases are narrower and not going to be generally useful. In that case I find the balance shifts to having more natural language like code that specifically names the purpose that the functions provides.

As previously pointed out… it’s really personal or team preference about how/when to the use either of the two approaches. Just don’t let it get you into a bikeshedding trap :slight_smile: .

5 Likes