ruby on rails - Are you allowed to use a find_each query with an includes statement? -

example:

foobar.joins(:baz).includes(:baz).count => 22926 foobar.joins(:baz).includes(:baz).find_each.count => 998 foobar.joins(:baz).find_each.count => 22926 

the generated sql in correct case (third) several batches of sql looks like:

select  "foobar".* "foobar" inner join "baz" on  "baz"."foobar_id" = "foobar"."id" order "foobar"."id" asc limit $1 

in failing (second) case there single query looks like:

select  "foobar"."id" t0_r0  "baz"."id" t1_r0  "baz"."foobar_id" t1_r1  "foobar" inner join "baz" on "baz"."foobar_id" = "foobar"."id"   order "foobar"."id" asc limit $1 

where of fields listed different temporary variable (e.g. t0_r0) different columns on each table (in actual query there 37 split 30 on first object, 7 on second).

is bug? includes not allowed in find_each query? doing wrong?

the relationship between foobar , baz foobar has_one baz , baz belongs_to foobar.

this problem can happen if has_one relationship isn't has_one.

say database doesn't have unique index on column baz.foobar_id. accidentally end situation have foobar record connected more 1 baz record:

baz.id | baz.foobar_id ------   ------------- 1        1 2        1 3        2 

in situation, joins return combination of foobar , baz records:

foobar.joins(:baz).count  # 3 

this means find_each join iterate 3 times , repeat 1 of foobar ids:

foobar.joins(:baz).find_each(batch_size: 2) { |f| puts f.id } # select  "foobar".* "foobar" inner join "baz" on... limit 2 1 1 # select  "foobar".* "foobar" inner join "baz" on... ("foobar"."id" > 1) ... limit 2 2 

adding in includes means rails going try consolidate results set of distinct foobar records. won't work how find_each manages batches:

foobar.joins(:baz).includes(:baz).find_each(batch_size: 2) { |f| puts f.id } # select  "foobar"."id" t0_r0 ... limit 2 1 

and @ point find_each stop processing because has found batch smaller batch size, thinks done:

# activerecord::batches#in_batches break if ids.length < batch_limit 

the default batch size find_each 1,000. problem case returned 998 records. indicates first batch loaded 998 unique foobar ids less batch size, , find_each thought done. loaded 1,000 baz records connected 998 distinct foobar records.

you may want review baz table see if has duplicate entries. can like:

baz.group(:foobar_id).having('count(*) > 1') 

the best solution use unique index avoid duplicates in database , enforce has_one relationship. alternative ensure you're getting distinct set of foobar records like:

foobar.group(:id).joins(:baz).includes(:baz).count foobar.group(:id).joins(:baz).includes(:baz).find_each.count foobar.group(:id).joins(:baz).find_each.count