ActiveRecordでINNER JOINしつつEagerLoadingしたいんです
対象の表に対してふつうに.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"
:
:
:
(^ω^)にこっ
ソート順違うのが気になる木
