#5294 Rails3 RC - Associations (has_one, has_many) does not work with models that are based on database views. - Ruby on Rails

Type	To find
responsible:me	tickets assigned to you
tagged:"@high"	tickets tagged @high
milestone:next	tickets in the upcoming milestone
state:invalid	tickets with the state invalid
created:"last week"	tickets created last week
sort:number, importance, updated	tickets sorted by #, importance or updated
Combine keywords for powerful searching.
Use advanced searching »

This project is archived and is in readonly mode.

#5294 ✓resolved

Rails3 RC - Associations (has_one, has_many) does not work with models that are based on database views.

Reported by Robert Pankowecki | August 4th, 2010 @ 11:46 AM | in 3.0.2


class Model < ActiveRecord::Base
  has_one :property, :class_name => "My", :foreign_key => :id
  has_many :properties, :class_name => "My", :foreign_key => :id
end

class My < ActiveRecord::Base
  set_table_name :my_view
end

ruby-1.9.2-head >  ActiveRecord::Migration.create_table "models", :force => true do |t|
ruby-1.9.2-head >    t.string   "name"
ruby-1.9.2-head >    t.datetime "created_at"
ruby-1.9.2-head >    t.datetime "updated_at"
ruby-1.9.2-head >  end

ruby-1.9.2-head > ActiveRecord::Base.connection.execute "Create view my_view as select * from models"

ruby-1.9.2-head > Model.create!(:name => 'name')

ruby-1.9.2-head > Model.first.property

ActiveRecord::StatementInvalid: SQLite3::SQLException: near "FROM": syntax error: SELECT      FROM       "my_view" WHERE     ("my_view".id = 1) LIMIT 1
    from /home/rupert/.rvm/gems/ruby-1.9.2-head/gems/activerecord-3.0.0.rc/lib/active_record/connection_adapters/abstract_adapter.rb:202:in `rescue in log'
    from /home/rupert/.rvm/gems/ruby-1.9.2-head/gems/activerecord-3.0.0.rc/lib/active_record/connection_adapters/abstract_adapter.rb:194:in `log'
    from /home/rupert/.rvm/gems/ruby-1.9.2-head/gems/activerecord-3.0.0.rc/lib/active_record/connection_adapters/sqlite_adapter.rb:135:in `execute'
    from /home/rupert/.rvm/gems/ruby-1.9.2-head/gems/activerecord-3.0.0.rc/lib/active_record/connection_adapters/sqlite_adapter.rb:284:in `select'
    from /home/rupert/.rvm/gems/ruby-1.9.2-head/gems/activerecord-3.0.0.rc/lib/active_record/connection_adapters/abstract/database_statements.rb:7:in `select_all'
    from /home/rupert/.rvm/gems/ruby-1.9.2-head/gems/activerecord-3.0.0.rc/lib/active_record/connection_adapters/abstract/query_cache.rb:56:in `select_all'
    from /home/rupert/.rvm/gems/ruby-1.9.2-head/gems/activerecord-3.0.0.rc/lib/active_record/base.rb:427:in `find_by_sql'
    from /home/rupert/.rvm/gems/ruby-1.9.2-head/gems/activerecord-3.0.0.rc/lib/active_record/relation.rb:64:in `to_a'
    from /home/rupert/.rvm/gems/ruby-1.9.2-head/gems/activerecord-3.0.0.rc/lib/active_record/relation/finder_methods.rb:324:in `find_first'
    from /home/rupert/.rvm/gems/ruby-1.9.2-head/gems/activerecord-3.0.0.rc/lib/active_record/relation/finder_methods.rb:117:in `first'
    from /home/rupert/.rvm/gems/ruby-1.9.2-head/gems/activerecord-3.0.0.rc/lib/active_record/relation/finder_methods.rb:100:in `find'
    from /home/rupert/.rvm/gems/ruby-1.9.2-head/gems/activerecord-3.0.0.rc/lib/active_record/relation/finder_methods.rb:96:in `find'
    from /home/rupert/.rvm/gems/ruby-1.9.2-head/gems/activerecord-3.0.0.rc/lib/active_record/base.rb:399:in `find'
    from /home/rupert/.rvm/gems/ruby-1.9.2-head/gems/activerecord-3.0.0.rc/lib/active_record/associations/has_one_association.rb:82:in `find_target'
    from /home/rupert/.rvm/gems/ruby-1.9.2-head/gems/activerecord-3.0.0.rc/lib/active_record/associations/association_proxy.rb:237:in `load_target'
    from /home/rupert/.rvm/gems/ruby-1.9.2-head/gems/activerecord-3.0.0.rc/lib/active_record/associations/association_proxy.rb:118:in `reload'
    from /home/rupert/.rvm/gems/ruby-1.9.2-head/gems/activerecord-3.0.0.rc/lib/active_record/associations.rb:1364:in `block in association_accessor_methods'
    from (irb):2
    from /home/rupert/.rvm/gems/ruby-1.9.2-head/gems/railties-3.0.0.rc/lib/rails/commands/console.rb:44:in `start'
    from /home/rupert/.rvm/gems/ruby-1.9.2-head/gems/railties-3.0.0.rc/lib/rails/commands/console.rb:8:in `start'
    from /home/rupert/.rvm/gems/ruby-1.9.2-head/gems/railties-3.0.0.rc/lib/rails/commands.rb:23:in `<top (required)>'
    from script/rails:6:in `require'



    from script/rails:6:in `<main>'

ruby-1.9.2-head > Model.first.properties

ActiveRecord::StatementInvalid: SQLite3::SQLException: near "FROM": syntax error: SELECT      FROM       "my_view" WHERE     ("my_view".id = 1)
    from /home/rupert/.rvm/gems/ruby-1.9.2-head/gems/activerecord-3.0.0.rc/lib/active_record/connection_adapters/abstract_adapter.rb:202:in `rescue in log'
    from /home/rupert/.rvm/gems/ruby-1.9.2-head/gems/activerecord-3.0.0.rc/lib/active_record/connection_adapters/abstract_adapter.rb:194:in `log'
    from /home/rupert/.rvm/gems/ruby-1.9.2-head/gems/activerecord-3.0.0.rc/lib/active_record/connection_adapters/sqlite_adapter.rb:135:in `execute'
    from /home/rupert/.rvm/gems/ruby-1.9.2-head/gems/activerecord-3.0.0.rc/lib/active_record/connection_adapters/sqlite_adapter.rb:284:in `select'
    from /home/rupert/.rvm/gems/ruby-1.9.2-head/gems/activerecord-3.0.0.rc/lib/active_record/connection_adapters/abstract/database_statements.rb:7:in `select_all'
    from /home/rupert/.rvm/gems/ruby-1.9.2-head/gems/activerecord-3.0.0.rc/lib/active_record/connection_adapters/abstract/query_cache.rb:56:in `select_all'
    from /home/rupert/.rvm/gems/ruby-1.9.2-head/gems/activerecord-3.0.0.rc/lib/active_record/base.rb:427:in `find_by_sql'
    from /home/rupert/.rvm/gems/ruby-1.9.2-head/gems/activerecord-3.0.0.rc/lib/active_record/relation.rb:64:in `to_a'
    from /home/rupert/.rvm/gems/ruby-1.9.2-head/gems/activerecord-3.0.0.rc/lib/active_record/relation/finder_methods.rb:138:in `all'
    from /home/rupert/.rvm/gems/ruby-1.9.2-head/gems/activerecord-3.0.0.rc/lib/active_record/associations/association_collection.rb:70:in `block in find'
    from /home/rupert/.rvm/gems/ruby-1.9.2-head/gems/activerecord-3.0.0.rc/lib/active_record/base.rb:1076:in `with_scope'
    from /home/rupert/.rvm/gems/ruby-1.9.2-head/gems/activerecord-3.0.0.rc/lib/active_record/associations/association_proxy.rb:203:in `with_scope'
    from /home/rupert/.rvm/gems/ruby-1.9.2-head/gems/activerecord-3.0.0.rc/lib/active_record/associations/association_collection.rb:63:in `find'
    from /home/rupert/.rvm/gems/ruby-1.9.2-head/gems/activerecord-3.0.0.rc/lib/active_record/associations/association_collection.rb:467:in `find_target'
    from /home/rupert/.rvm/gems/ruby-1.9.2-head/gems/activerecord-3.0.0.rc/lib/active_record/associations/association_collection.rb:409:in `load_target'
    from /home/rupert/.rvm/gems/ruby-1.9.2-head/gems/activerecord-3.0.0.rc/lib/active_record/associations/association_proxy.rb:145:in `inspect'
    from /home/rupert/.rvm/gems/ruby-1.9.2-head/gems/railties-3.0.0.rc/lib/rails/commands/console.rb:44:in `start'
    from /home/rupert/.rvm/gems/ruby-1.9.2-head/gems/railties-3.0.0.rc/lib/rails/commands/console.rb:8:in `start'
    from /home/rupert/.rvm/gems/ruby-1.9.2-head/gems/railties-3.0.0.rc/lib/rails/commands.rb:23:in `<top (required)>'
    from script/rails:6:in `require'
    from script/rails:6:in `<main>'ruby-1.9.2-head >

As you can see the generated sql query is invalid. There is nothing but spaces between SELECT and FROM words.
It worked fine on Rails3.beta4
I tested that the bug occurs on sqlite3 and postgresql, did not tested other databases.

Comments and changes to this ticket

You flagged this item as spam.
Carl Tashian August 18th, 2010 @ 07:53 PM
With postgresql this errors out slightly differently:

Model.size

SQL (0.8ms) SELECT COUNT(*) FROM pg_tables WHERE tablename = 'v_my_view_name'

NoMethodError: undefined method size' for Model(Table doesn't exist):Class<br/>
```
from /Users/carl/.rvm/gems/ruby-1.9.2-rc2@og/gems/activerecord-3.0.0.rc/lib/active_record/base.rb:963:in `method_missing'
```
This could very well be a bug in the pg gem, though!
You flagged this item as spam.
Robert Pankowecki August 18th, 2010 @ 07:57 PM
For me the error was exactly the same on postgresql and sqlite. Maybe something changed in later version...
You flagged this item as spam.
Sen August 23rd, 2010 @ 08:02 AM
i got the same error on mysql2.
Andrew White August 23rd, 2010 @ 01:02 PM
- Assigned user set to “Aaron Patterson”
- Importance changed from “” to “Low”
Hey guys, can someone come up with a test case for this please?
RC2 is coming soon and if you'd like this fixed the help would be appreciated.
You flagged this item as spam.
Robert Pankowecki August 23rd, 2010 @ 07:56 PM
Yes, we can! :-) I can do it tomorrow (CET).
Jeremy Kemper August 27th, 2010 @ 11:21 PM
- Milestone cleared.
- State changed from “new” to “open”
Aaron Patterson August 29th, 2010 @ 03:31 AM
- State changed from “open” to “needs-more-info”
Using the code provided in this ticket and ruby 1.9.2-p0, I was not able to reproduce this bug.

I am working against arel master and rails master. Would you mind trying with edge rails / arel?
Jeremy Kemper August 30th, 2010 @ 04:10 AM
- Milestone cleared.
[bulk edit]

Robert Pankowecki August 30th, 2010 @ 06:23 PM

How can I use latest rails in my project to reproduce it ?
This is in my Gemfile:

gem 'rails', :git => 'git://github.com/rails/rails.git'

but running

bundle install

leads to:

Updating git://github.com/rails/rails.git
Fetching source index for http://rubygems.org/
Bundler could not find compatible versions for gem "activesupport":
 In Gemfile:
 rails depends on
  activesupport (~> 3.0.0)
 rails depends on
  activesupport (3.1.0.beta)

Neeraj Singh August 30th, 2010 @ 07:20 PM
Fix is mentioned here.

https://rails.lighthouseapp.com/projects/8994-ruby-on-rails/tickets...
You flagged this item as spam.
Robert Pankowecki August 31st, 2010 @ 08:31 PM
I checked that it works fine with Rails 3.0 final release and with master. You can close it.
Aaron Patterson September 28th, 2010 @ 06:18 PM
- State changed from “needs-more-info” to “resolved”
Closing per Robert's comment. :-)
Jeremy Kemper October 15th, 2010 @ 11:02 PM
- Milestone set to “3.0.2”
[bulk edit]