ActiveRecordでサブクエリのIN句を書きたいんです
ruby 1.9.2p290 (2011-07-09) [i386-mingw32] activerecord (3.1.0) activerecord-oracle_enhanced-adapter (1.4.2)
IN句自体はバインド変数を配列にすれば勝手にいいようにしてくれるようです。
サブクエリはArelを徹底活用するのがいいようです。“ふつうに”書いたのではクエリを2回投げてしまうようですね。
Oracle 11g XE HRスキーマにて、サラリーが最大9000ドルなJOBの従業員をEMPLOYEESから抽出する。
サブクエリ使わない版
ids = Job.where(max_salary: 9000).all.map{|record| record.job_id} Employee.where(job_id: ids).each do |record| p record end
D, [2013-06-20T01:26:08.936587 #9180] DEBUG -- : jobs 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 = 'JOBS' AND hidden_column = 'NO' ORDER BY column_id
D, [2013-06-20T01:26:08.950588 #9180] DEBUG -- : (14.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-20T01:26:08.952588 #9180] 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 = 'JOBS' AND c.constraint_type = 'P' AND cc.owner = c.owner AND cc.constraint_name = c.constraint_name
D, [2013-06-20T01:26:08.957588 #9180] DEBUG -- : Job Load (4.0ms) SELECT "JOBS".* FROM "JOBS" WHERE "JOBS"."MAX_SALARY" = 9000
D, [2013-06-20T01:26:08.959588 #9180] 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 = 'JOBS' AND c.constraint_type = 'P' AND cc.owner = c.owner AND cc.constraint_name = c.constraint_name
D, [2013-06-20T01:26:08.966588 #9180] DEBUG -- : employees 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 = 'EMPLOYEES' AND hidden_column = 'NO' ORDER BY column_id
D, [2013-06-20T01:26:08.969589 #9180] 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 = 'EMPLOYEES' AND c.constraint_type = 'P' AND cc.owner = c.owner AND cc.constraint_name = c.constraint_name
D, [2013-06-20T01:26:08.971589 #9180] DEBUG -- : Employee Load (2.0ms) SELECT "EMPLOYEES".* FROM "EMPLOYEES" WHERE "EMPLOYEES"."JOB_ID" IN ('FI_ACCOUNT', 'AC_ACCOUNT', 'MK_REP', 'HR_REP')
D, [2013-06-20T01:26:08.974589 #9180] 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 = 'EMPLOYEES' AND c.constraint_type = 'P' AND cc.owner = c.owner AND cc.constraint_name = c.constraint_name
#<Employee employee_id: 206, first_name: "William", last_name: "Gietz", email: "WGIETZ", phone_number: "515.123.8181", hire_date: "2002-06-07 00:00:00", job_id: "AC_ACCOUNT", salary: #<BigDecimal:2e2dab8,'0.83E4',4(8)>, commission_pct: nil, manager_id: 205, department_id: 110>
#<Employee employee_id: 109, first_name: "Daniel", last_name: "Faviet", email: "DFAVIET", phone_number: "515.124.4169", hire_date: "2002-08-16 00:00:00", job_id: "FI_ACCOUNT", salary: #<BigDecimal:2e2c5b8,'0.9E4',4(8)>, commission_pct: nil, manager_id: 108, department_id: 100>
#<Employee employee_id: 110, first_name: "John", last_name: "Chen", email: "JCHEN", phone_number: "515.124.4269", hire_date: "2005-09-28 00:00:00", job_id: "FI_ACCOUNT", salary: #<BigDecimal:2e2ac08,'0.82E4',4(8)>, commission_pct: nil, manager_id: 108, department_id: 100>
#<Employee employee_id: 111, first_name: "Ismael", last_name: "Sciarra", email: "ISCIARRA", phone_number: "515.124.4369", hire_date: "2005-09-30 00:00:00", job_id: "FI_ACCOUNT", salary: #<BigDecimal:2e19ac0,'0.77E4',4(8)>, commission_pct: nil, manager_id: 108, department_id: 100>
#<Employee employee_id: 112, first_name: "Jose Manuel", last_name: "Urman", email: "JMURMAN", phone_number: "515.124.4469", hire_date: "2006-03-07 00:00:00", job_id: "FI_ACCOUNT", salary: #<BigDecimal:2e186f8,'0.78E4',4(8)>, commission_pct: nil, manager_id: 108, department_id: 100>
#<Employee employee_id: 113, first_name: "Luis", last_name: "Popp", email: "LPOPP", phone_number: "515.124.4567", hire_date: "2007-12-07 00:00:00", job_id: "FI_ACCOUNT", salary: #<BigDecimal:2e173f0,'0.69E4',4(8)>, commission_pct: nil, manager_id: 108, department_id: 100>
#<Employee employee_id: 203, first_name: "Susan", last_name: "Mavris", email: "SMAVRIS", phone_number: "515.123.7777", hire_date: "2002-06-07 00:00:00", job_id: "HR_REP", salary: #<BigDecimal:2e16208,'0.65E4',4(8)>, commission_pct: nil, manager_id: 101, department_id: 40>
#<Employee employee_id: 202, first_name: "Pat", last_name: "Fay", email: "PFAY", phone_number: "603.123.6666", hire_date: "2005-08-17 00:00:00", job_id: "MK_REP", salary: #<BigDecimal:2e12548,'0.6E4',4(8)>, commission_pct: nil, manager_id: 201, department_id: 20>
2回selectしています。こう書いても同じです。
Employee.where(job_id: Job.where(max_salary: 9000).all.map{|record| record.job_id})
サブクエリ使う版
j = Job.arel_table Employee.where(job_id: j.project(:job_id).where(j[:max_salary].eq(9000))).each do |record| p record end
D, [2013-06-20T01:32:24.848088 #9296] DEBUG -- : employees 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 = 'EMPLOYEES' AND hidden_column = 'NO' ORDER BY column_id
D, [2013-06-20T01:32:24.862088 #9296] 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-20T01:32:24.865089 #9296] 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 = 'EMPLOYEES' AND c.constraint_type = 'P' AND cc.owner = c.owner AND cc.constraint_name = c.constraint_name
D, [2013-06-20T01:32:24.869089 #9296] DEBUG -- : jobs 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 = 'JOBS' AND hidden_column = 'NO' ORDER BY column_id
D, [2013-06-20T01:32:24.872089 #9296] 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 = 'JOBS' AND c.constraint_type = 'P' AND cc.owner = c.owner AND cc.constraint_name = c.constraint_name
D, [2013-06-20T01:32:24.878089 #9296] DEBUG -- : Employee Load (6.0ms) SELECT "EMPLOYEES".* FROM "EMPLOYEES" WHERE "EMPLOYEES"."JOB_ID" IN (SELECT job_id FROM "JOBS" WHERE "JOBS"."MAX_SALARY" = 9000)
D, [2013-06-20T01:32:24.881090 #9296] 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 = 'EMPLOYEES' AND c.constraint_type = 'P' AND cc.owner = c.owner AND cc.constraint_name = c.constraint_name
#<Employee employee_id: 109, first_name: "Daniel", last_name: "Faviet", email: "DFAVIET", phone_number: "515.124.4169", hire_date: "2002-08-16 00:00:00", job_id: "FI_ACCOUNT", salary: #<BigDecimal:2e66938,'0.9E4',4(8)>, commission_pct: nil, manager_id: 108, department_id: 100>
#<Employee employee_id: 110, first_name: "John", last_name: "Chen", email: "JCHEN", phone_number: "515.124.4269", hire_date: "2005-09-28 00:00:00", job_id: "FI_ACCOUNT", salary: #<BigDecimal:2e5f108,'0.82E4',4(8)>, commission_pct: nil, manager_id: 108, department_id: 100>
#<Employee employee_id: 111, first_name: "Ismael", last_name: "Sciarra", email: "ISCIARRA", phone_number: "515.124.4369", hire_date: "2005-09-30 00:00:00", job_id: "FI_ACCOUNT", salary: #<BigDecimal:2e5d8c0,'0.77E4',4(8)>, commission_pct: nil, manager_id: 108, department_id: 100>
#<Employee employee_id: 112, first_name: "Jose Manuel", last_name: "Urman", email: "JMURMAN", phone_number: "515.124.4469", hire_date: "2006-03-07 00:00:00", job_id: "FI_ACCOUNT", salary: #<BigDecimal:2e59d50,'0.78E4',4(8)>, commission_pct: nil, manager_id: 108, department_id: 100>
#<Employee employee_id: 113, first_name: "Luis", last_name: "Popp", email: "LPOPP", phone_number: "515.124.4567", hire_date: "2007-12-07 00:00:00", job_id: "FI_ACCOUNT", salary: #<BigDecimal:2e583d0,'0.69E4',4(8)>, commission_pct: nil, manager_id: 108, department_id: 100>
#<Employee employee_id: 206, first_name: "William", last_name: "Gietz", email: "WGIETZ", phone_number: "515.123.8181", hire_date: "2002-06-07 00:00:00", job_id: "AC_ACCOUNT", salary: #<BigDecimal:2e56c90,'0.83E4',4(8)>, commission_pct: nil, manager_id: 205, department_id: 110>
#<Employee employee_id: 202, first_name: "Pat", last_name: "Fay", email: "PFAY", phone_number: "603.123.6666", hire_date: "2005-08-17 00:00:00", job_id: "MK_REP", salary: #<BigDecimal:2e537c8,'0.6E4',4(8)>, commission_pct: nil, manager_id: 201, department_id: 20>
#<Employee employee_id: 203, first_name: "Susan", last_name: "Mavris", email: "SMAVRIS", phone_number: "515.123.7777", hire_date: "2002-06-07 00:00:00", job_id: "HR_REP", salary: #<BigDecimal:2e521f0,'0.65E4',4(8)>, commission_pct: nil, manager_id: 101, department_id: 40>
(^ω^)にこっ
ちゃんとサブクエリを使って1回でselectしています。
まぎらわしいことに、select使うときはカラム名を配列で指定する必要があるのにprojectの場合は配列にしてはいけない。なぜか知らないし調べてもいないけど。
ソースを探すコマンド
余談だけど
PS:Desktop> gem which logger D:/RailsInstaller/Ruby1.9.2/lib/ruby/1.9.1/logger.rb
