db-calls-in-views.md

I'm defining a function on Question model called user, which gets the User record tied to it.

Do you think multiple calls to a DB in the view/template is too much?

So there's the "best practices" answer, and then what I imagine is the actual answer to your question here.

The Actual Answer

So let's assume we have a Question model:

class Question < ActiveRecord::Base
  belongs_to :user
end

A controller that fetches all questions:

class QuestionsController < ApplicationController
  def index
    @questions = Question.all
  end
end

And a corresponding view to render each question in a table row, with the question text in the first cell and the user name in the second:

<!--  app/views/questions/index.html.erb  --> 

<table>
  <tbody>
    <% @questions.each do |question|  %>
      <tr>

        <td> <%= question.text %> </td>

        <td> <%= question.user.name %> </td>

      </tr>
    <% end %>
  </tbody>
</table>

Because of the belongs_to :user in Question, the question.user.name gets us what we want, but it also results in a lot of excess SQL queries. It goes like so:

1. in QuestionsController – one query for all Questions
2. in the view – one query for each User

So if we have 10 questions, the code above will result in the execution of 11 database queries.

Instead, since we know we're going to want access to question.user in our view, we can use ActiveRecord::QueryMethods::includes in our controller to fetch them more efficiently:

class QuestionsController < ApplicationController
  def index
    @questions = Question.includes(:user)
  end
end

Note that this is lazy, returning an ActiveRecord::Relation, and actually executes SQL at the point in our view when we call @questions.each.

To take a look at the actual SQL executed for this, let's compare the two calls in rails console for a project of mine where a User has_many :entries:

# USER IDS

User.pluck(:id)
#  (0.3ms)  SELECT "users"."id" FROM "users"
# => [2, 3, 1]


# SIMULATED CRAP WAY

User.all.map(&:entries)
#  User Load (0.3ms)  SELECT "users".* FROM "users"
#  Entry Load (0.2ms)  SELECT "entries".* FROM "entries"  WHERE "entries"."user_id" = $1  [["user_id", 2]]
#  Entry Load (0.1ms)  SELECT "entries".* FROM "entries"  WHERE "entries"."user_id" = $1  [["user_id", 3]]
#  Entry Load (0.1ms)  SELECT "entries".* FROM "entries"  WHERE "entries"."user_id" = $1  [["user_id", 1]]
# => [...] # array of `Entry`s


# PERFORMANT WAY

User.includes(:entries)
#  User Load   (0.4ms)  SELECT "users".* FROM "users"
#  Entry Load  (0.2ms)  SELECT "entries".* FROM "entries"  WHERE "entries"."user_id" IN (2, 3, 1)
# => [...] # array of `User`s with associated `Entry` objects preloaded



# NOTE: things are eagerly loaded above by the REPL to have something to display but...

User.includes(:entries).class
# => User::ActiveRecord_Relation

Here we can see that the User::includes class method leaves us with 1 + 1 queries, instead of 1 + N where N = number_of_users.

Ecto (Elixir)

In Ecto, assuming that we had a User schema ("model", essentially) and an Authentication schema, this might look like:

defmodule Question do
  use App.Schema

  schema "questions" do
    # ...

    belongs_to :user, User
  end
end

# PRELOAD USER AUTHENTICATIONS

Repo.all from u in User, preload: :authentications
# [debug] QUERY OK source="users" db=5.9ms decode=4.6ms
# SELECT u0."id", u0."first_name", u0."last_name", u0."inserted_at", u0."updated_at" FROM "users" AS u0 []
# [debug] QUERY OK source="authentications" db=1.6ms
# SELECT a0."id", a0."provider", a0."uid", a0."user_id", a0."inserted_at", a0."updated_at", a0."user_id" FROM "authentications" AS a0 WHERE (a0."user_id" = $1) ORDER BY a0."user_id" [1]
# => [...] # list of `User` structs with `Authentication`s preloaded


# WITHOUT PRELOAD

Repo.get User, 1
# [debug] QUERY OK source="users" db=4.1ms
# SELECT u0."id", u0."first_name", u0."last_name", u0."inserted_at", u0."updated_at" FROM "users" AS u0 WHERE (u0."id" = $1) [1]
# => %App.User{
#      first_name: "Brad"
#      last_name: "Chase",
#      authentications: #Ecto.Association.NotLoaded<association :authentications is not loaded>,
#      inserted_at: ~N[2017-01-25 22:39:28.000000],
#      updated_at: ~N[2017-01-25 22:39:28.000000]
#    }

Best Practices

So that's generally how things are done in ActiveRecord and Ecto, but one idea I'm pretty sure is becoming more and more popular is: more than 0 DB calls in your views is too much.

In Ecto, this is sorta just how things are, since, as you can see above, not preloading authentications leaves you with a struct that basically has an error telling you that it has no authentications loaded. Your only recourse then is to preload these in your Repo call (well, or otherwise make them available to the template from the controller) or to call Repo directly in the template, which, for me at least, would set off serious alarm bells of "I really don't want to be doing this..."

In ActiveRecord, you aren't thinking about making the database calls in the view, but you technically are, which is quite arguably not a big deal, but I personally like the separation provided by the "my controllers fetch and mutate my persisted data, and my templates display it" method.

Okay, Rails...

I was going to say that you could force things to be eager in your Rails controller with:

@questions = Question.includes(:user).all

But, this is now apparently a thing...

User.includes(:entries).all.class
# => User::ActiveRecord_Relation

So I honestly don't know how you'd even go about forcing the SQL execution (sanely) in your Rails controllers now.

Or, admittedly, if it's even big enough of a deal to bother trying to force it.

Presenters

Relatedly, I think the separation mentioned above is also a key motivation behind "presenters", but I admittedly haven't looked deeply into the pattern. Actually, now that I'm thinking about it, I believe the motivating factor may instead be around encapsulating your display logic. Either way, something to look into.

Also, Avdi talks about "exhibits" in this portion of Objects on Rails, which I remember really liking, despite only partially following.

Conclusion

... I honestly don't know what the best practice is!

That's how I'd personally do it with ActiveRecord and Ecto, though.