Simon G. 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 G. 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.
- 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.
- 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).
- 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