Inject by SQL

Background

In a Rails application we sometimes need to execute complicated aggregation queries especially for admin screen. For example, in a Search User page in admin screen, we might want to show

• The last login time of each user.
• The number of times each user logged in.

in the search result. These kind of queries often happens in a search result in admin screen or CSV download functionality. We need to implement it without N+1 problem.

These aggregation queries can be easily implemented if we use caching mechanisms like counter_cache. But having cache columns for the sake of admin screen's minor requirements and placing loads on user's page does not sound a good idea.

Another possible solution is to use find_by_sql. At first glance it works well, but we will soon realize that it's not easy to cooperate well with complicated search form implemented with ransack because it uses scope based query. And we will suffer from nasty bugs which only happens in combination with only some of the search conditions. We want things to be decoupled.

In such a case, we can use the following inject_by_sql method and can implement aggregation query as a reusable, independent query.

Solution

First we need to define inject_by_sql as a class method of all ActiveRecord model classes.

module ActiveRecord
  module InjectionExt
    extend ActiveSupport::Concern

    module ClassMethods
      def inject_by_sql( models, sql, options = {} )
        sanitized = sanitize_sql( [sql, options.merge(ids: models.map(&:id))])
        result = self.connection.execute sanitized
        models_by_id = models.index_by(&:id)

        fields = result.fields

        raise "Duplicate columns: #{fields.inspect}" if fields.uniq.size != fields.size
        raise "Blank column name is detected: #{fields.inspect}" if fields.any?(&:blank?)

        index_of_id = fields.index("id")
        raise "There is not `id` column in the result." if index_of_id.nil?

        result.each do |row|
          id = row[index_of_id]
          next if id.nil?
          model = models_by_id[id]
          unless model
            raise "Model could not find for id=#{row[index_of_id].inspect}"
          end
          fields.zip(row).each do |key,value|
            next if key == "id"
            model[key] = value
          end
        end

        models.each do |m|
          fields.each do |k|
            m[k] = nil if m[k].nil? # make key
          end
        end

        models
      end

    end
  end
  class Base
    include InjectionExt
  end
end

It just execute the given query and then inject the result value to the models using []= method of ActiveRecord. Note that the SQL has to have a field named id to work.

With this method, we can inject last_logined_at and number_of_login as follows.

class User
  class << self
    def inject_login_stats( users )
      sql = <<-SQL
        SELECT user_id AS id,
               max(created_at) as last_logined_at,
               count(*) AS number_of_login
          FROM login_logs
         GROUP BY user_id
      SQL
      User.inject_by_sql( users, sql )
    end
  end
end

And you can call this class method like this

@search = User.ransack(params[:search]).result
@users = @search.to_a
User.inject_login_stats( @users )

Now you can access these injected values like this.

- @users.each do |u|
  tr
    td= u.name
    td= u.last_logined_at
    td= u.number_of_login

TODO

It would be happier if we can call injection methods in a similar manner with like preloads. For example,

User.inject(:number_of_login).ransack(:params)