読者です 読者をやめる 読者になる 読者になる

ActiveRecordでINNER JOINしつつEagerLoadingしたいんです

rails active record

対象の表に対してふつうに.joins()して.includes()すればいいようです。

ruby 1.9.2p290 (2011-07-09) [i386-mingw32]
activerecord (3.1.0)
activerecord-oracle_enhanced-adapter (1.4.2)

railsでinnner joinするクエリを元に一覧表示画面を表示する場合、放っておくと一覧の1行を生成するために1回クエリが発行され、n+1問題が発生する。

すなわち

q = Country.joins(:region)
logger.info q.to_sql
q.each do |country|
  p "#{country.country_name} - #{country.region.region_name}"
end
D, [2013-06-22T16:24:19.831293 #4188] DEBUG -- :    (13.0ms)  SELECT DECODE(table_name, UPPER(table_name), LOWER(table_name), table_name) FROM all_tables WHERE owner = SYS_CONTEXT('userenv', 'session_user') AND secondary = 'N'
D, [2013-06-22T16:24:19.834293 #4188] DEBUG -- :   countries Columns (2.0ms)  SELECT column_name AS name, data_type AS sql_type, data_default, nullable, virtual_column, hidden_column, DECODE(data_type, 'NUMBER', data_precision, 'FLOAT', data_precision, 'VARCHAR2', DECODE(char_used, 'C', char_length, data_length), 'RAW', DECODE(char_used, 'C', char_length, data_length), 'CHAR', DECODE(char_used, 'C', char_length, data_length), NULL) AS limit, DECODE(data_type, 'NUMBER', data_scale, NULL) AS scale FROM all_tab_cols WHERE owner = 'HR' AND table_name = 'COUNTRIES' AND hidden_column = 'NO' ORDER BY column_id
D, [2013-06-22T16:24:19.836293 #4188] DEBUG -- :   Primary Key (2.0ms)  SELECT cc.column_name FROM all_constraints c, all_cons_columns cc WHERE c.owner = 'HR' AND c.table_name = 'COUNTRIES' AND c.constraint_type = 'P' AND cc.owner = c.owner AND cc.constraint_name = c.constraint_name
D, [2013-06-22T16:24:19.838293 #4188] DEBUG -- :   regions Columns (1.0ms)  SELECT column_name AS name, data_type AS sql_type, data_default, nullable, virtual_column, hidden_column, DECODE(data_type, 'NUMBER', data_precision, 'FLOAT', data_precision, 'VARCHAR2', DECODE(char_used, 'C', char_length, data_length), 'RAW', DECODE(char_used, 'C', char_length, data_length), 'CHAR', DECODE(char_used, 'C', char_length, data_length), NULL) AS limit, DECODE(data_type, 'NUMBER', data_scale, NULL) AS scale FROM all_tab_cols WHERE owner = 'HR' AND table_name = 'REGIONS' AND hidden_column = 'NO' ORDER BY column_id
D, [2013-06-22T16:24:19.841293 #4188] DEBUG -- :   Primary Key (2.0ms)  SELECT cc.column_name FROM all_constraints c, all_cons_columns cc WHERE c.owner = 'HR' AND c.table_name = 'REGIONS' AND c.constraint_type = 'P' AND cc.owner = c.owner AND cc.constraint_name = c.constraint_name
I, [2013-06-22T16:24:19.841293 #4188]  INFO -- : SELECT "COUNTRIES".* FROM "COUNTRIES" INNER JOIN "REGIONS" ON "REGIONS"."REGION_ID" = "COUNTRIES"."REGION_ID"
D, [2013-06-22T16:24:19.843293 #4188] DEBUG -- :   Country Load (1.0ms)  SELECT "COUNTRIES".* FROM "COUNTRIES" INNER JOIN "REGIONS" ON "REGIONS"."REGION_ID" = "COUNTRIES"."REGION_ID"
D, [2013-06-22T16:24:20.073307 #4188] DEBUG -- :   Region Load (2.0ms)  SELECT "REGIONS".* FROM "REGIONS" WHERE "REGIONS"."REGION_ID" = 2.0 AND ROWNUM <= 1
"Argentina - Americas"
D, [2013-06-22T16:24:20.077307 #4188] DEBUG -- :   Region Load (1.0ms)  SELECT "REGIONS".* FROM "REGIONS" WHERE "REGIONS"."REGION_ID" = 3.0 AND ROWNUM <= 1
"Australia - Asia"
D, [2013-06-22T16:24:20.079307 #4188] DEBUG -- :   Region Load (1.0ms)  SELECT "REGIONS".* FROM "REGIONS" WHERE "REGIONS"."REGION_ID" = 1.0 AND ROWNUM <= 1
"Belgium - Europe"
D, [2013-06-22T16:24:20.080307 #4188] DEBUG -- :   Region Load (0.0ms)  SELECT "REGIONS".* FROM "REGIONS" WHERE "REGIONS"."REGION_ID" = 2.0 AND ROWNUM <= 1
"Brazil - Americas"
:
:
:

こんなの嫌なのでinner joinしつつeager loadingしたかったのですが、しばらく考えて「ふつうに.joins()して.includes()すればよくね?」と思いつき、やってみたらできました。

すなわち

q = Country.joins(:region).includes(:region)
logger.info q.to_sql
q.each do |country|
  p "#{country.country_name} - #{country.region.region_name}"
end
D, [2013-06-22T16:27:29.195124 #2628] DEBUG -- :    (13.0ms)  SELECT DECODE(table_name, UPPER(table_name), LOWER(table_name), table_name) FROM all_tables WHERE owner = SYS_CONTEXT('userenv', 'session_user') AND secondary = 'N'
D, [2013-06-22T16:27:29.198124 #2628] DEBUG -- :   countries Columns (2.0ms)  SELECT column_name AS name, data_type AS sql_type, data_default, nullable, virtual_column, hidden_column, DECODE(data_type, 'NUMBER', data_precision, 'FLOAT', data_precision, 'VARCHAR2', DECODE(char_used, 'C', char_length, data_length), 'RAW', DECODE(char_used, 'C', char_length, data_length), 'CHAR', DECODE(char_used, 'C', char_length, data_length), NULL) AS limit, DECODE(data_type, 'NUMBER', data_scale, NULL) AS scale FROM all_tab_cols WHERE owner = 'HR' AND table_name = 'COUNTRIES' AND hidden_column = 'NO' ORDER BY column_id
D, [2013-06-22T16:27:29.200124 #2628] DEBUG -- :   Primary Key (1.0ms)  SELECT cc.column_name FROM all_constraints c, all_cons_columns cc WHERE c.owner = 'HR' AND c.table_name = 'COUNTRIES' AND c.constraint_type = 'P' AND cc.owner = c.owner AND cc.constraint_name = c.constraint_name
D, [2013-06-22T16:27:29.202124 #2628] DEBUG -- :   regions Columns (1.0ms)  SELECT column_name AS name, data_type AS sql_type, data_default, nullable, virtual_column, hidden_column, DECODE(data_type, 'NUMBER', data_precision, 'FLOAT', data_precision, 'VARCHAR2', DECODE(char_used, 'C', char_length, data_length), 'RAW', DECODE(char_used, 'C', char_length, data_length), 'CHAR', DECODE(char_used, 'C', char_length, data_length), NULL) AS limit, DECODE(data_type, 'NUMBER', data_scale, NULL) AS scale FROM all_tab_cols WHERE owner = 'HR' AND table_name = 'REGIONS' AND hidden_column = 'NO' ORDER BY column_id
D, [2013-06-22T16:27:29.205124 #2628] DEBUG -- :   Primary Key (2.0ms)  SELECT cc.column_name FROM all_constraints c, all_cons_columns cc WHERE c.owner = 'HR' AND c.table_name = 'REGIONS' AND c.constraint_type = 'P' AND cc.owner = c.owner AND cc.constraint_name = c.constraint_name
I, [2013-06-22T16:27:29.205124 #2628]  INFO -- : SELECT "COUNTRIES".* FROM "COUNTRIES" INNER JOIN "REGIONS" ON "REGIONS"."REGION_ID" = "COUNTRIES"."REGION_ID"
D, [2013-06-22T16:27:29.208124 #2628] DEBUG -- :   SQL (1.0ms)  SELECT "COUNTRIES"."COUNTRY_ID" AS t0_r0, "COUNTRIES"."COUNTRY_ID" AS t0_r1, "COUNTRIES"."COUNTRY_NAME" AS t0_r2, "COUNTRIES"."REGION_ID" AS t0_r3, "REGIONS"."REGION_ID" AS t1_r0, "REGIONS"."REGION_ID" AS t1_r1, "REGIONS"."REGION_NAME" AS t1_r2 FROM "COUNTRIES" INNER JOIN "REGIONS" ON "REGIONS"."REGION_ID" = "COUNTRIES"."REGION_ID"
"Netherlands - Europe"
"France - Europe"
"United Kingdom - Europe"
"Denmark - Europe"
"Belgium - Europe"
:
:
:

(^ω^)にこっ

ソート順違うのが気になる木