Reduce N+1 queries in serialisation with assosiations

Question

Models:

class Audio < ActiveRecord::Base
  has_many :tests, as: :item
end

class Video < ActiveRecord::Base
  has_many :tests, as: :item
end

class Test < ActiveRecord::Base
  belongs_to :user
  belongs_to :item, polymorphic: true
end

class User < ActiveRecord::Base
  has_many :tests

  def score_for(item)
    return 0 unless tests.where(item: item).any?

    tests.where(item: item).last.score
  end
end

Serializers:

class VideoSerializer < ActiveModel::Serializer
  attributes :id, :name
  attribute(:score) { user.score_for(object) }

  def user
    instance_options[:user]
  end
end

I try serialise lot of Video objects like this, but N+1 coming:

options = { each_serializer: VideoSerializer, user: User.last }
videos = ActiveModelSerializers::SerializableResource.new(Video.all, options).serializable_hash

If I try this, empty array returned(looks like videos not has tests for this user):

options = { each_serializer: VideoSerializer, user: User.last }
videos = ActiveModelSerializers::SerializableResource.new(Video.includes(:tests).where(tests: {user: User.last}), options).serializable_hash

How I can organise serialisation w/o N+1 queries problem.

Answer 1

You cannot avoid an N+1 query if you are using a method that triggers another SQL query (in this case where).

The method score_for does another query (or 2, which would definitely need refactoring) when you invoke the relation with where.

One way you could change this method would be not to use relation methods but array methods over already loaded relations. This is very inefficient for memory but much less heavy on DB.

def score_for(item)
  tests.sort_by&:created_at).reverse.find { |test| test.user_id == id }&.score.to_f
end

You would need to load the video with its tests and the user.