Scopeing your quries

Scope

In this application there are a couple of ways to give a user rights to do things and to show them things. These can be different contexts of the application, contexts are mostly evolved around entities and admins can do and see everything there. Next to that users can be assigned to these entities, when users are assigned they can see all their assigned entities in these contexts and also gain the right to perform actions over these entities.

To make sure users can only access the entities that they are assigned to we use authorize/3 from Bodyguard, to only show the entities that users are allowed to see we use the scope/4 function.

Preloads

Every trip to the database has some overhead and when we do this too much our application can become slow. To make sure that it doesn’t become slow we fetch al our data from the database in the controllers, so loops in views cannot cause n+1 queries. But then we’re still left with quite a bunch of database calls and we try to limit those by using Ecto.Query.preload/3 in our queries. This makes sure that when can get the entities with their related entities and their entities.

This makes sure that in 1 roundtrip to the database we can get a number of items. The only problem here is that by preloading entities of entities we can too easily get preloaded entities that shouldn’t be shown to users. To fix this we could create a new function of every type of relation but that would become a burden. Instead of that we created a scope on the preloads, this is based on the works of the Bodyguard.scope/4 function.

Using Preload

For this we created a Preload library, to use this in the app. We can add Preload as a dependency.

0
1
2
3
4
5
6
defp deps do
  [
    ...
    {:preload, "~> 0.1.0"},
    ...
  ]
end

After running mix deps.get we can add Preload.scope/4 to the queries we’re interested in. Since we would mostly use this to get the right preloads based on the controller we add an argument called options to our function. From the options we fetch preload and context.

  • preload: Tells us what it is that we want to preload
  • context: Tells us from where in the app we want to preload this.
0
1
2
3
4
5
6
7
def get_jobs(user, options \\ %{}) do
  preload = Map.get(options, :preload, [])
  context = Map.get(options, :context, :user)

  Job
  |> Preload.scope(preload, user, %{context: context})
  |> Repo.all
end

The first argument in Preload.scope has to be queryable with ecto and should therefor be a module/atom or an Ecto query. The second argument is normaly an atom or a list of atoms that need to be preloaded on the query. The user is the third argument of the Preload.scope/4, we need the user to know how strictly we need to scope, what the user can and cannot see from the preloads. The fourth argument is optional and can be Map to add the context or other options you might like, this can be used to maybe show or hide archived/unpublished entities.

This function can be called from the controller with:

0
1
2
3
4
def index(conn, params) do
  ...
  jobs = get_job(current_user, %{preload: [:managers, :company], context: :admin})
  ...
end

Now the preload can be implemented. For this a .Scope module has to be created that has the function def scope_on_preload/4. For the function get_jobs that we created we could implmented this in the following way.

0
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
defmodule MyApp.Jobs.Job.Scope do
  import Ecto.Query

  @whitelisted_preloads ~w(company)a

  def scope_on_preload(query, :managers, %User{}, %{context: :user}) do
    Managers
    |> where(active: true)
    |> order_by(:updated_at)

    preload(query, managers: ^managers_preload)
  end

  def scope_on_preload(query, :managers, %User{role: :admin}, %{context: :admin}) do
    Managers
    |> order_by(:updated_at)

    preload(query, managers: ^managers_preload)
  end

  def scope_on_preload(query, preload, _, _) when preload in @whitelisted_preloads,
    do: preload(query, ^preload)
end

In this example we see the managers can be preloaded for both the user context and the admin context. Where the pattern match contains %{context: :user} only the active managers are listed and in the admin context all managers are returned. At both places we order the query based on updated_at field of managers.

With the @whitelisted_preloads check on the last scope_on_preload it is made sure that only :company can be preload, this way we cannot accidentally preload more from the controller than we would like.