Eager loading only first page of results with active record

Question

I am working on a feature to search for items.

My model is this, roughtly:

User has many items
User has many addresses
User has one profile
Item has many images
Item has many addresses
Item belongs to category

I want to display the results grouped by user, eg:

Results for 'Laptop':

Items of Bob:
  Laptop dell (details...)
  Samsung laptop (details...)

Items of Alice:
  Laptop HP (details...)
...

So I have this big query with eager loading:

r = User.includes([{items: [:images, :addresses, :category]}, :addresses, :profile]).
  joins(:items).
  where('query like "%laptop%"').
  where(...).
  limit(80).
  all
# then get the first page of results with kaminary

And then I display with a loop like this in erb:

r.each do |u|
  # items of user u
  u.items do |i|
    # item i
  end
end

Everything works fine, except that the eager loading takes a looong time. It could be much faster by eager loading only the items on the first page that are displayed.

It is possible with active record to eager load only records with a condition ? Something like: User.includes([:table1, table2], conditions: 'users.id in (1,2,3)') ?

Another solution would be to execute the big request with a limit 20, and then redo the request without eager loading with a limit 80, and then manually merge the results, but this is complicated ?

Any other suggestions ?

score 0 · Answer 1 · answered Aug 31 '13 at 16:54

I ended up with doing two requests and merging the results together. It is not as hard as it sounds:

# per_page is 20
limit = "#{(page.to_i - 1) * 20}, #{(page.to_i * 20)}"
query = Users.where('items.title LIKE "%whatever%"').where('addresses.lat < 50')
return query.includes([{items: [:images, :category]}, :profile, :addresses]).
    joins(:items).          
    limit(limit).all |
    query.select('DISTINCT users.*').joins(:items, :addresses).
    limit(200).all

Remark 1:

This eager loads only the 20 elements of the current page, and adds all the elements on the other pages without eager loading. For example, say you are on page 3, the first request would eager loads with limit 60, 80 and elements #0 to #199 are added. The way array union (operator |) works in ruby is that if the same element is present in both arrays, the element in the first array is kept. In this case, the element in the first array would the the element eager loaded. So if we are on page three, it would merge [60..80] (eager loaded) with [1..200] (not eager loaded), and the elements 60 to 80 in the final array would be the ones in the first array.

Remark 2:

The select distinct is important, because without it the limit would be limiting on the number of items or addresses. For example, if user #1 has 198 objects, the second query would return something like this:

  user 1 - item 1 of user 1
  user 1 - item 2 of user 1
  ...
  user 1 - item 198 of user 1
  user 2 - item 1 of user 2
  user 3 - item 1 of user 3

And that wouldn't preload correctly. We would have users [1, 2, 3, 60, 61, ... 80] after the merge.

With the distinct, the results would be:

  user 1 - item 1 of user 1
  user 2 - item 1 of user 2
  user 3 - item 1 of user 3
  ...
  user 200 - item 1 of user 200

And we would have [1, 2, 3... 200] after the merge, with elements 60 to 80 taken from the first query.

Remark 3:

In the second query, we have to join all the tables used in the where clauses.

Eager loading only first page of results with active record

1 Answers1