0

I'm trying to eager load relationship data via the Sequel gem's eager method while using with_sql to define the sql used for the query (it's a fairly complex query). The eager loading doesn't work at all. Instead it produces a single query per relationship for each row returned, which is what I'm trying to avoid. Here's some code:

class Foo
  many_to_one :rel1
  one_to_many :rel2
  def self.bar
    sql = 'some complex sql'
    Foo.with_sql(sql).eager(:rel1, :rel2)
  end
end

The problem is that when I call Foo.bar, the eager loading of rel1 and rel2 are seemingly ignored. I've verified this by watching the log - for each row returned by the with_sql call, it generates 2 more queries - one for rel1 and one for rel2.

It may be worth noting that sql contains 3 joins.

Is this a limitation of using with_sql. Is there a way around this limitation?

user2571294
  • 91
  • 7
  • Why are you using the `eager` gem? The SQL should "eager load" by itself. One thing that you can do is update the question with a simplified query, perhaps with a single join. – B Seven Aug 26 '16 at 16:13

2 Answers2

0

Turns out this wasn't a limitation of with_sql. The problem was that there was a limit being added to the query downstream, and that forces the eager loading to be ignored. Calling Foo.bar worked fine, but if you limit the output via limit(<integer>), it necessarily negates any eager loading since the sql query itself contains multiple joins. My understanding of the Sequel docs indicates that the eager loading can't work in this situation. If that's not the case, I'd love to hear about it.

user2571294
  • 91
  • 7
  • Although, interestingly, Sequel's documentation specifically states "Eager also works on a limited dataset..." So the mystery remains. – user2571294 Aug 26 '16 at 19:39
  • Finally got this sorted. You can use eager loading with a limit, but the order of the calls appears to matter. If I do this: `with_sql(sql).limit(per_page).offset(per_page * (page - 1)).eager(:rel1, :rel2).all`, it works. If I put the `limit` and `offset` after the call to `eager`, it does NOT work. Interesting. In any case, just super-happy to finally get this working. – user2571294 Aug 26 '16 at 20:15
0

Note that eager in Sequel does eager loading using a query per association. You probably want to use eager_graph, which uses joins. You can use eager_graph with with_sql, though you will probably also need to use set_graph_aliases to tell Sequel how to split up each row into separate objects. Also, make sure you call all on the dataset to get results, you can't eager load a dataset without having all records up front.

In terms of limiting, you can eager load a dataset that uses limit, as long as you call all on it, but note that it generally will not do what you want if you are eagerly loading a *_many association.

Jeremy Evans
  • 11,959
  • 27
  • 26