Simon Grant wrote in post #969223:> Say that I want to create a web form that contains only a few fields
> from a table of 100 fields. To use a model from the table, I would have
> to read all the fields of the model (through an ActiveModel::find ) to
> populate the form. Then when I want to update the values of the form
> back to the database, will not all the 100 field values of the model be
> updated? or just the ones changed. Seems to be very inefficient. I only
> want to read and write the values of the form that I need.
It is often more efficient to reply below the already quoted text of the
discussion. So I will continue my reply further below.
> Marnen Laibow-Koser wrote in post #969070:
>> Simon Grant wrote in post #968977:
>>> I have a database table with over 100 fields, however I want my
model
>>> to include only a few fields so that every time I do a
query/update, it
>>> will only do so for those fields.
>>
>> Why? What''s the point?
>>
>>> Is there a way to specify in a model to only use selected fields
from a
>>> table and ignore the rest?
>>
>> Well, you could use the :select option, or (depending on your use case)
>> attr_protected might help. But I don''t see why
you''re bothering.
Sorry long post, covering 3 techniques.
1. SELECT:
=========As Marnen points out above, the :select option could be used to limit
the columns that are actually read in from the database into the
instantiated ruby objects.
The :select feature in Rails, is not my favorite technique (I even
consider certain behaviour a bug), see the code below.
(Rails 3.0.3; Ubuntu 10.04; system ruby 1.8.7):
$ cat app/models/payment.rb
class Payment < ActiveRecord::Base
belongs_to :user
end
$ head -2 app/models/user.rb
class User < ActiveRecord::Base
has_many :payments
...
$ rails console
Loading development environment (Rails 3.0.3)
irb(main):001:0> user_chris =
User.find_by_first_name(''Chris'')
=> #<User id: 2, first_name: "Chris", ...
# SELECT `users`.* FROM `users` WHERE (`users`.`first_name` =
''Chris'')
LIMIT 1
# [off-topic]: this returns a random pick of the users named
''Chris''
irb(main):002:0> user_chris.payments
=> [#<Payment id: 5, user_id: 2, testing: "beta", created_at:
"2010-12-18 08:58:10", updated_at: "2010-12-18 08:58:10",
amount: nil>,
#<Payment id: 6, user_id: 2, testing: "beta", created_at:
"2010-12-18
09:06:27", updated_at: "2010-12-18 09:06:27",
amount: #<BigDecimal:b6c10dac,''0.12345E3'',8(12)>>]
# SELECT `payments`.* FROM `payments` WHERE (`payments`.user_id = 2)
# this loads associated payments with all their columns
irb(main):003:0> p5 = Payment.find(5)
=> #<Payment id: 5, user_id: 2, testing: "beta", created_at:
"2010-12-18
08:58:10", updated_at: "2010-12-18 08:58:10", amount: nil>
# SELECT `payments`.* FROM `payments` WHERE (`payments`.`id` = 5) LIMIT
1
irb(main):004:0> p6 = Payment.find(6, :select => [:id, :testing])
=> #<Payment id: 6, testing: "beta">
# SELECT id, testing FROM `payments` WHERE (`payments`.`id` = 6) LIMIT 1
# this has loaded only the id and testing column
irb(main):005:0> p5.user
=> #<User id: 2, first_name: "Chris", last_name:
"Goods", user_name:
nil, testing: "beta", created_at: "2010-12-18 08:55:14",
updated_at:
"2010-12-18 08:55:14">
# SELECT `users`.* FROM `users` WHERE (`users`.`id` = 2) LIMIT 1
# using the belongs_to association on p5 works (since :user_id present)
irb(main):006:0> p6.testing
=> "beta"
# got this from the internal object (no call to database)
irb(main):007:0> p6.amount
ActiveModel::MissingAttributeError: missing attribute: amount
from (irb):7
# ActiveRecord gives an error to say that it had not loaded the
# ''amount'' column (good)
irb(main):008:0> p6.user
=> nil
# without a warning, ActiveRecord tells me that there is no user
# for this payment ... (in the db, there _is_ user "Chris")
# I personally consider this a bug. I would have expected:
# * either I get the MissingAttributeError (as for ''amount'')
# * or I get the information lazily loaded from the database
# for the work-around, see below
irb(main):009:0> p5.amount = 87.23
=> 87.23
# this works as expected
irb(main):010:0> p6.amount = 99.23
=> 99.23
# this is a little strange. Asking p6.amount gives an
# ActiveModel::MissingAttributeError for the reader
# but the setter is available and works.
# I would have expected also a MissingAttributeError here
irb(main):011:0> p6.amount
=> #<BigDecimal:b6bedd70,''0.9923E2'',8(8)>
# and now the reader is present ...
irb(main):012:0> p6.save
=> true
# SQL (0.1ms) BEGIN
# AREL (0.4ms) UPDATE `payments` SET `amount` = 99.23, `updated_at` =
''2010-12-18 09:15:49'' WHERE (`payments`.`id` = 6)
# SQL (1.1ms) COMMIT
# this works and also writes the amount
# IMPORTANT: it only writes the changed columns back to the database
# you find those with .changed? and .change functions
To make it cleaner, I added a default_scope to the Payment model
and User model. Trying again.
$ head -3 app/models/user.rb
class User < ActiveRecord::Base
default_scope :order => :id
has_many :payments
$ cat app/models/payment.rb
class Payment < ActiveRecord::Base
belongs_to :user
default_scope :select => [:id, :user_id, :testing], :order =>
:created_at
# important : include ALL foreign keys in the select
# this is my work-around to avoid the problem that payment.user
# silently fails if user_id was not included in the select
end
$ rails console
Loading development environment (Rails 3.0.3)
irb(main):001:0> tom = User.find_by_first_name(''Tom'')
=> #<User id: 1, first_name: "Tom", last_name:
"Smith", user_name: nil,
testing: "alfa", created_at: "2010-12-18 08:52:32",
updated_at:
"2010-12-18 08:52:32">
# SQL (0.3ms) SHOW TABLES
# User Load (0.1ms) SELECT `users`.* FROM `users` WHERE
(`users`.`first_name` = ''Tom'') ORDER BY id LIMIT 1
# now the default_scope on User makes sure we do not get a random
# pick from the users named "Tom", but we get a consistent result
irb(main):002:0> tom.payments
=> [#<Payment id: 1, user_id: 1, testing: "alfa">,
#<Payment id: 2,
user_id: 1, testing: "beta">, #<Payment id: 3, user_id: 1,
testing:
"gamma">, #<Payment id: 4, user_id: 1, testing:
"peta">]
# Payment Load (0.2ms) SELECT id, user_id, testing FROM `payments`
WHERE (`payments`.user_id = 1) ORDER BY created_at
# the payments now have the SELECT clause automatically applied,
# also to the user.payments association :-)
irb(main):003:0> p1 = tom.payments.first
=> #<Payment id: 1, user_id: 1, testing: "alfa">
# Payment Load (0.5ms) SELECT id, user_id, testing FROM `payments`
WHERE (`payments`.user_id = 1) ORDER BY created_at LIMIT 1
# because of the :order in the default scope, this first
# yields a repeatable result
irb(main):004:0> p3, p4 = tom.payments.limit(2).offset(2)
=> [#<Payment id: 3, user_id: 1, testing: "gamma">,
#<Payment id: 4,
user_id: 1, testing: "peta">]
# Payment Load (0.1ms) SELECT id, user_id, testing FROM `payments`
WHERE (`payments`.user_id = 1) ORDER BY created_at LIMIT 1 OFFSET 2
# or use limit and offset for a window.
irb(main):005:0> p1.amount
ActiveModel::MissingAttributeError: missing attribute: amount
from
/usr/lib/ruby/gems/1.8/gems/activerecord-3.0.3/lib/active_record/attribute_methods.rb:44:in
`send''
from
/usr/lib/ruby/gems/1.8/gems/activerecord-3.0.3/lib/active_record/attribute_methods.rb:44:in
`method_missing''
from (irb):5
# this fails, as expected (no SQL generated)
irb(main):006:0> p1.amount = 33.99
=> 33.99
# this I find confusing in combination with the line above
irb(main):007:0> p1.amount
=> #<BigDecimal:b6c63818,''0.3399E2'',8(8)>
# now this works
irb(main):008:0> p1.save
=> true
# SQL (0.1ms) BEGIN
# AREL (0.4ms) UPDATE `payments` SET `amount` = 33.99, `updated_at` =
''2010-12-18 09:52:02'' WHERE (`payments`.`id` = 1)
# SQL (2.3ms) COMMIT
# this works, and only updates the amount column (and the updated_at).
irb(main):009:0> p1.user
=> #<User id: 1, first_name: "Tom", last_name:
"Smith", user_name: nil,
testing: "alfa", created_at: "2010-12-18 08:52:32",
updated_at:
"2010-12-18 08:52:32">
# this works now :-) because the default scope included user_id
My conclusions for :select:
* :select can be used to limit the access to a table with many columns,
mainly for _performance_ reasons.
* It will only give limited protection against accidental access to
unintended columns. The read accessor will throw an exception, but
the write accessor for not selected columns will work and a save
afterwards will UPDATE in the database (which may not have been
the design intention).
* always select all foreign keys for which you have a belongs_to
association defined. Otherwise the association will report no
associated objects, which may be different from the state in the
database.
* a default_scope is a clean way to centralize the :select and
:order clauses.
2. attr_protected:
=================
This relates to the fact that a mass-assignment is or is not possible.
Certainly useful to reduce the chance for accidentally writing to a
not-selected column.
Changed the user model:
class User < ActiveRecord::Base
default_scope :order => :id
attr_protected :user_name
has_many :payments
...
end
On the User model the effect is this:
$ rails console
Loading development environment (Rails 3.0.3)
irb(main):001:0> u1 = User.first
=> #<User id: 1, first_name: "Tom", last_name:
"Smith", ...>
# User Load (0.3ms) SELECT `users`.* FROM `users` ORDER BY id LIMIT 1
irb(main):002:0> u1.attributes
=> {"created_at"=>Sat Dec 18 08:52:32 UTC 2010,
"updated_at"=>Sat Dec 18
10:27:04 UTC 2010, "id"=>1,
"user_name"=>"tom_smith",
"testing"=>"alfa",
"last_name"=>"Smith",
"first_name"=>"Tom"}
# attributes returns a hash with the attributes
irb(main):003:0> u1.attributes={:first_name => "David",
:user_name =>
"david_smith"}
=> {:user_name=>"david_smith",
:first_name=>"David"}
# WARNING: Can''t mass-assign protected attributes: user_name
# writing to attributes is similar to what happens on build, create etc.
# but, we get a warning here: the value of user_name is not changed
# in the object
irb(main):004:0> u1
=> #<User id: 1, first_name: "David", last_name:
"Smith", user_name:
"tom_smith", testing: "alfa", created_at: "2010-12-18
08:52:32",
updated_at: "2010-12-18 10:27:04">
# the value of :user_name is not changed in memory
irb(main):005:0> u1.save
=> true
# SQL (0.1ms) BEGIN
# SQL (0.4ms) SHOW TABLES
# AREL (0.4ms) UPDATE `users` SET `first_name` = ''David'',
`updated_at`
= ''2010-12-18 10:30:26'' WHERE (`users`.`id` = 1)
# SQL (2.0ms) COMMIT
# only the first_name was updates in this context
irb(main):006:0> u1.user_name = "david_smith"
=> "david_smith"
# this will work (with a direct assignment)
irb(main):007:0> u1.save
=> true
# SQL (0.1ms) BEGIN
# AREL (0.4ms) UPDATE `users` SET `user_name` =
''david_smith'',
`updated_at` = ''2010-12-18 10:35:16'' WHERE (`users`.`id` = 1)
# SQL (1.7ms) COMMIT
If you have many columns, but want to allow _automatic_ write
access to only a few, use a white listing technique
(attr_accessible that is).
3. Overriding the write accessors:
=================================
If you want to protect some columns from writing to, I find
reasonable protection in overwriting the write accessors.
In the Payments model.
$ cat app/models/payment.rb
class Payment < ActiveRecord::Base
belongs_to :user
default_scope :select => [:id, :user_id, :testing], :order =>
:created_at
def amount= amount
raise RuntimeError, ''Trying to change the "amount"
column''
end
end
The result now is that read and write accessors are blocked:
$ rails console
Loading development environment (Rails 3.0.3)
irb(main):001:0> p1 = Payment.first
=> #<Payment id: 1, user_id: 1, testing: "alfa">
# Payment Load (0.1ms) SELECT id, user_id, testing FROM `payments`
ORDER BY created_at LIMIT 1
irb(main):002:0> p1.amount
ActiveModel::MissingAttributeError: missing attribute: amount
from
/usr/lib/ruby/gems/1.8/gems/activerecord-3.0.3/lib/active_record/attribute_methods.rb:44:in
`send''
from
/usr/lib/ruby/gems/1.8/gems/activerecord-3.0.3/lib/active_record/attribute_methods.rb:44:in
`method_missing''
from (irb):2
# read accessor is blocked since column not read
irb(main):003:0> p1.amount= 12.55
RuntimeError: Trying to change the "amount" column
from
/home/peterv/data/back/rails-apps/apps/db/app/models/payment.rb:6:in
`amount=''
from (irb):3
# write accessor is blocked in the model
irb(main):004:0> p1.testing= ''beta''
=> "beta"
irb(main):005:0> p1.save
=> true
# SQL (0.1ms) BEGIN
# SQL (0.3ms) SHOW TABLES
# AREL (0.3ms) UPDATE `payments` SET `updated_at` = ''2010-12-18
10:44:59'', `testing` = ''beta'' WHERE (`payments`.`id`
= 1)
# SQL (1.9ms) COMMIT
# and this stil works
Maybe it would be better to also explicitly override the read
accessor. Then we are not dependent on the :select to block
access to those non-relevant columns.
General conclusion:
==================* if the goal is "performance optimization",
don''t even bother now
* if the goal is not accidentally reading/writing to the non-relevant
columns, override the accessors in the model.
* maybe the database "view" technique is really simpler.
HTH,
Peter
--
Posted via http://www.ruby-forum.com/.
--
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en.