Railsのfind_by_sqlとSTIについて
STI 時に find_by_sql で返すインスタンスのクラスについて調べてみました。activerecord-1.13.2 と activerecord-2.3.11 で挙動を調べました。
activerecord-1.13.2/lib/active_record/base.rb
427 # Works like find(:all), but requires a complete SQL string. Examples: 428 # Post.find_by_sql "SELECT p.*, c.author FROM posts p, comments c WHERE p.id = c.post_id" 429 # Post.find_by_sql ["SELECT * FROM posts WHERE author = ? AND created > ?", author_id, start_date] 430 def find_by_sql(sql) 431 connection.select_all(sanitize_sql(sql), "#{name} Load").collect! { |record| instantiate(record) } 432 end 885 # Finder methods must instantiate through this method to work with the single-table inheritance model 886 # that makes it possible to create objects of different types from the same table. 887 def instantiate(record)
activerecord-2.3.11/lib/active_record/base.rb
642 # Executes a custom SQL query against your database and returns all the results. The results will 643 # be returned as an array with columns requested encapsulated as attributes of the model you call 644 # this method from. If you call <tt>Product.find_by_sql</tt> then the results will be returned in 645 # a Product object with the attributes you specified in the SQL query. 646 # 647 # If you call a complicated SQL query which spans multiple tables the columns specified by the 648 # SELECT will be attributes of the model, whether or not they are columns of the corresponding 649 # table. 650 # 651 # The +sql+ parameter is a full SQL query as a string. It will be called as is, there will be 652 # no database agnostic conversions performed. This should be a last resort because using, for example, 653 # MySQL specific terms will lock you to using that particular database engine or require you to 654 # change your call if you switch engines. 655 # 656 # ==== Examples 657 # # A simple SQL query spanning multiple tables 658 # Post.find_by_sql "SELECT p.title, c.author FROM posts p, comments c WHERE p.id = c.post_id" 659 # > [#<Post:0x36bff9c @attributes={"title"=>"Ruby Meetup", "first_name"=>"Quentin"}>, ...] 660 # 661 # # You can use the same string replacement techniques as you can with ActiveRecord#find 662 # Post.find_by_sql ["SELECT title FROM posts WHERE author = ? AND created > ?", author_id, start_date] 663 # > [#<Post:0x36bff9c @attributes={"first_name"=>"The Cheap Man Buys Twice"}>, ...] 664 def find_by_sql(sql) 665 connection.select_all(sanitize_sql(sql), "#{name} Load").collect! { |record| instantiate(record) } 666 end 1651 # Finder methods must instantiate through this method to work with the 1652 # single-table inheritance model that makes it possible to create 1653 # objects of different types from the same table. 1654 def instantiate(record)
前提データ
usersテーブル
id | type | name | |||
1 | HogeUser | bob | |||
2 | FugaUser | mike |
fooテーブル
id | title | ||
1 | baz |
Railsにあるクラス
class User < ActiveRecord::Base end class HogeUser < User end class FugaUser < User end class Foo < ActiveRecord::Base end
activerecord-1.13.2/lib/active_record/base.rb の実行結果
typeを見て、HogeUser、FugaUserのインスタンスになります。
% script/console >> user = User.find_by_sql("select * from users") >> user.each {|u| p u.class } HogeUser FugaUser
type カラムを select から抜くと STI の効果はなくなります。
% script/console user = User.find_by_sql("select id, name from users") user.each {|u| p u.class } User User
Fooクラスでfind_by_sqlしても、typeカラムがあれば、HogeUser、FugaUserのインスタンスになります。
% script/console user = Foo.find_by_sql("select * from users") user.each {|u| p u.class } HogeUser FugaUser
typeをselectからなくして、Fooクラスでfind_by_sqlすれば、Fooクラスのインスタンスになります。
% script/console user = Foo.find_by_sql("select id, name from users") user.each {|u| p u.class } Foo Foo
もし、HogeUserクラス、FugaUserクラスが存在しない場合。ActiveRecord::SubclassNotFound が出ます。
% script/console >> User.find_by_sql("select * from users") User Load (0.000236) select * from users ActiveRecord::SubclassNotFound: The single-table inheritance mechanism failed to locate the subclass: 'HogeUser'. This error is raised because the column 'type' is reserved for storing the class in case of inheritance. Please rename this column if you didn't intend it to be used for storing the inheritance class or overwrite User.inheritance_column to use another column for that information. from /usr/local/lib/ruby/gems/1.8/gems/activerecord-1.13.2/lib/active_record/base.rb:897:in `instantiate_without_callbacks' from /usr/local/lib/ruby/gems/1.8/gems/activerecord-1.13.2/lib/active_record/callbacks.rb:215:in `instantiate' from /usr/local/lib/ruby/gems/1.8/gems/activerecord-1.13.2/lib/active_record/base.rb:431:in `find_by_sql' from /usr/local/lib/ruby/gems/1.8/gems/activerecord-1.13.2/lib/active_record/base.rb:431:in `collect!' from /usr/local/lib/ruby/gems/1.8/gems/activerecord-1.13.2/lib/active_record/base.rb:431:in `find_by_sql' from (irb):1
activerecord-2.3.11/lib/active_record/base.rb の実行結果
typeを見て、HogeUser、FugaUserのインスタンスになります。
% script/console >> user = User.find_by_sql("select * from users") >> user.each {|u| p u.class } HogeUser(id: integer, type: string, name: string) FugaUser(id: integer, type: string, name: string)
type カラムを select から抜くと STI の効果はなくなります。
% script/console user = Foo.find_by_sql("select * from users") user.each {|u| p u.class } User(id: integer, type: string, name: string) User(id: integer, type: string, name: string)
Fooクラスを経由すると、Fooクラスとなります。ここは、activerecord-1.13.2とは違う挙動です。むしろ、find_by_sql がより直感的な挙動になったと思います。
% script/console user = Foo.find_by_sql("select * from users") user.each {|u| p u.class } Foo(id: integer, title: string) Foo(id: integer, title: string)
もし、HogeUserクラスとFugaUserクラスがActiveRecord::Baseを継承していた場合は hoge_users(fuga_users)テーブルがないとエラーが出ます。(activerecord-1.13.2ではこのエラーは出ません)
% script/console >> User.find_by_sql("select * from users") SQL (0.1ms) SET SQL_AUTO_IS_NULL=0 User Load (0.1ms) select * from users User Columns (0.2ms) SHOW FIELDS FROM `users` HogeUser Columns (0.0ms) Mysql::Error: Table 'find_by_sql.hoge_users' doesn't exist: SHOW FIELDS FROM `hoge_users` ActiveRecord::StatementInvalid: Mysql::Error: Table 'find_by_sql.hoge_users' doesn't exist: SHOW FIELDS FROM `hoge_users` from /usr/local/lib/ruby/gems/1.8/gems/activerecord-2.3.11/lib/active_record/connection_adapters/abstract_adapter.rb:227:in `log' from /usr/local/lib/ruby/gems/1.8/gems/activerecord-2.3.11/lib/active_record/connection_adapters/mysql_adapter.rb:324:in `execute' from /usr/local/lib/ruby/gems/1.8/gems/activerecord-2.3.11/lib/active_record/connection_adapters/mysql_adapter.rb:471:in `columns' from /usr/local/lib/ruby/gems/1.8/gems/activerecord-2.3.11/lib/active_record/base.rb:1305:in `columns' from /usr/local/lib/ruby/gems/1.8/gems/activerecord-2.3.11/lib/active_record/base.rb:1318:in `column_names' from /usr/local/lib/ruby/gems/1.8/gems/activerecord-2.3.11/lib/active_record/base.rb:2899:in `inspect' from /usr/local/lib/ruby/gems/1.8/gems/wirble-0.1.3/lib/wirble.rb:432:in `output_value' from /usr/local/lib/ruby/1.8/irb.rb:151:in `eval_input' from /usr/local/lib/ruby/1.8/irb.rb:263:in `signal_status' from /usr/local/lib/ruby/1.8/irb.rb:147:in `eval_input' from /usr/local/lib/ruby/1.8/irb.rb:146:in `eval_input' from /usr/local/lib/ruby/1.8/irb.rb:70:in `start' from /usr/local/lib/ruby/1.8/irb.rb:69:in `catch' from /usr/local/lib/ruby/1.8/irb.rb:69:in `start' from /usr/local/bin/irb:13
もし、HogeUserクラス、FugaUserクラスが存在しない場合。ActiveRecord::SubclassNotFound が出ます。
>> User.find_by_sql("select * from users") SQL (0.5ms) SET SQL_AUTO_IS_NULL=0 User Load (0.4ms) select * from users User Columns (0.3ms) SHOW FIELDS FROM `users` ActiveRecord::SubclassNotFound: The single-table inheritance mechanism failed to locate the subclass: 'HogeUser'. This error is raised because the column 'type' is reserved for storing the class in case of inheritance. Please rename this column if you didn't intend it to be used for storing the inheritance class or overwrite User.inheritance_column to use another column for that information. from /usr/local/lib/ruby/gems/1.8/gems/activerecord-2.3.11/lib/active_record/base.rb:1671:in `instantiate' from /usr/local/lib/ruby/gems/1.8/gems/activerecord-2.3.11/lib/active_record/base.rb:665:in `find_by_sql' from /usr/local/lib/ruby/gems/1.8/gems/activerecord-2.3.11/lib/active_record/base.rb:665:in `collect!' from /usr/local/lib/ruby/gems/1.8/gems/activerecord-2.3.11/lib/active_record/base.rb:665:in `find_by_sql' from (irb):1