6

I think I need something akin to a rails eager loaded query with a limit on it but I am having trouble finding a solution for that.

For the sake of simplicity, let us say that there will never be more than 30 Persons in the system (so Person.all is a small dataset) but each person will have upwards of 2000 comments (so Person.include(:comments) would be a large data set).

Parent association

class Person < ActiveRecord::Base
  has_many :comments
end

Child association

class Comment < ActiveRecord::Base
  belongs_to :person
end

I need to query for a list of Persons and include their comments, but I only need 5 of them.

I would like to do something like this:

Limited parent association

class Person < ActiveRecord::Base
  has_many :comments
  has_many :sample_of_comments, \
    :class_name => 'Comment', :limit => 5
end

Controller

class PersonController < ApplicationController
  def index
    @persons = Person.include(:sample_of_comments)
  end
end

Unfortunately, this article states: "If you eager load an association with a specified :limit option, it will be ignored, returning all the associated objects"

Is there any good way around this? Or am I doomed to chose between eager loading 1000s of unneeded ActiveRecord objects and an N+1 query? Also note that this is a simplified example. In the real world, I will have other associations with Person, in the same index action with the same issue as comments. (photos, articles, etc).

Chad M
  • 943
  • 1
  • 9
  • 22
  • regardless of rails or activerecord, first try to come up with the pair of SQLs that you think will give you your resultset. if you can't, then neither can any orm libraries. – choonkeat Apr 09 '12 at 16:34
  • @ChadM did you ever manage to get a solution to this problem? Or do you have some general advice that would point someone in the right direction? I too have the same problem: I do not want to eagerload 2000 comments :'( – BenKoshy Apr 23 '20 at 09:55
  • @BKSpurgeon Unfortunately, we did not. It turned out that our collection of Persons (per the example above) was going to be small enough per page that the N+1 query was the lesser of the two evils. If that had not been the case, we probably would have done either an `after_commit` similar to the accepted answer, or put some kind of trigger to do the same thing directly into the database. – Chad M Jul 17 '20 at 19:13

1 Answers1

0

Regardless of what "that article" said, the issue is in SQL you can't narrow down the second sql query (of eager loading) the way you want in this scenario, purely by using a standard LIMIT

You can, however, add a new column and perform a WHERE clause instead

  1. Change your second association to Person has_many :sample_of_comments, conditions: { is_sample: true }
  2. Add a is_sample column to comments table
  3. Add a Comment#before_create hook that assigns is_sample = person.sample_of_comments.count < 5
choonkeat
  • 5,557
  • 2
  • 26
  • 19
  • Hi choonkeet, it's not a bad idea. Unfortunately, for each comment the person creates, the set of comments that would be considered samples would shift by one. I suppose doing some kind of update all set of statements would work for this, but it seems like a heavy write. Example: Person has 5 comments, all are "samples". Person creates a 6th comment. Now, comment 1 should no longer be part of the sample set and 2 through 6 are now the sample comments set. – Chad M Sep 19 '15 at 19:25