Active Record :includes with limit

Question

I have a table of Articles which have many Images

The images are sorted for each Article with the first ordered image intended to be the article thumbnail.

In the Article controller index method I am currently doing the the following to limit to 2 active record queries;

@articles = Article.where(:active => true).includes(:images)

And to access the thumbnail:

# article model
def thumb
 self.images.first if self.images
end

Problem is this is only 2 queries, but if each article has 10 images and i have 50 articles on each page then ive loaded 500 image rows into memory.

Is there a more effective way of doing this in active record. Was hoping not to have to use find_by_sql

Answer 1

2 queries

@articles = Article.where(:active => true).includes(:thumb)

# app/model/article.rb
has_one :thumb, :class_name => 'Image',
                :conditions => { :ordinal => 1 }

1 query

Don't use this solution if some articles don't have thumb

If you want to specify a condition on the joined table, maybe you should use joins instead of includes:

@articles = Article.joins(:images)
                   .where(:active => true, 
                          :images => { :ordinal => 1 })
                   .includes(:images)

It loads only the images where :ordinal => 1

I hope it helps

The difference between joins and includes when specifying conditions on eager loaded associations:

"Even though Active Record lets you specify conditions on the eager loaded associations just like joins, the recommended way is to use joins instead.

[...]

includes generate a query which contains a LEFT OUTER JOIN whereas the joins method would generate one using the INNER JOIN function instead." For further help see the Specifying Conditions on Eager Loaded Associations section in Active Record Query Interface Guide.

Answer 2

This isn't the cleanest, but you can keep this down to 2 queries by selecting all images associated with the selected articles that have ordinal 1, like this:

@articles = Article.where(:active => true).all
images = Image.where(:article_id => @articles.map(&:id), :ordinal => 1).all

The won't link the objects together though. You can make thumb an attribute accessor though instead of a method, like this:

# article model
attr_accessor :thumb

Then when you are loading your images like above, you could set the thumb attribute after:

@articles = Article.where(:active => true).all
images = Image.where(:article_id => @articles.map(&:id), :ordinal => 1).all
images_hash = images.each_with_object({}) { |img, h| h[img.article_id] = img }
@articles.each { |article| article.thumb = images_hash[article.id] }

That's one way to do it. UPDATE: Improved efficiency of above code per @Axsuul's suggestion in the comments.

Another way, if you know that every article will have at least one image, is to come at from the images side. Select all images with ordinal 1, and include the articles. Like this:

@articles = Image.include(:article).where('images.ordinal' => 1, 'articles.active' => true).map(&:article)