Model fields

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.

Is there a way to specify in a model to only use selected fields from a
table and ignore the rest?

Thankyou.

Why do you want to do it?
You don’t want to give possibility to update some fiends to some
users?
Or you have some fields with a lot of data(BLOB) ?

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.

Thankyou.

Best,

Marnen Laibow-Koser
http://www.marnen.org
[email protected]

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.

Is there a way to specify in a model to only use selected fields from a
table and ignore the rest?

If you want hard protection for reading and writing only
to a few columns, you could use a VIEW. In that way,
you would also stay with the default Rails behaviour for
that limited set of columns.

mysql> CREATE TABLE many_columns (c1 text, c2 text, c3 text, c4 text);
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO many_columns (c1,c2,c3,c4) VALUES (‘t1’, ‘t2’, ‘t3’,
‘t4’);
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM many_columns;
±-----±-----±-----±-----+
| c1 | c2 | c3 | c4 |
±-----±-----±-----±-----+
| t1 | t2 | t3 | t4 |
±-----±-----±-----±-----+
1 row in set (0.00 sec)

mysql> CREATE VIEW less_columns AS SELECT c1, c2 FROM many_columns;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT * from less_columns;
±-----±-----+
| c1 | c2 |
±-----±-----+
| t1 | t2 |
±-----±-----+
1 row in set (0.00 sec)

mysql> INSERT INTO less_columns (c1,c2) VALUES (‘tl1’, ‘tl2’);
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * from less_columns;
±-----±-----+
| c1 | c2 |
±-----±-----+
| t1 | t2 |
| tl1 | tl2 |
±-----±-----+
2 rows in set (0.00 sec)

mysql> SELECT * from many_columns;
±-----±-----±-----±-----+
| c1 | c2 | c3 | c4 |
±-----±-----±-----±-----+
| t1 | t2 | t3 | t4 |
| tl1 | tl2 | NULL | NULL |
±-----±-----±-----±-----+
2 rows in set (0.00 sec)

HTH,

Peter

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.

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.

Thankyou.

Best,

Marnen Laibow-Koser
http://www.marnen.org
[email protected]

On 18 December 2010 05:49, Simon G. [email protected] wrote:

Please do not top post, it makes it difficult to follow the thread,
insert your comments into the previous post. Thanks

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.

You may think that that there is efficiency but do you know that it
actually matters in your case? I think you are proposing what is
commonly known as Premature Optimisation.

In my experience the bottlenecks in an app are rarely where you expect
them to be at the start. Design your app initially in the simplest
possible way, simple code means less bugs and less development time.
Use Test Driven Design so that you have automated tests that show that
all is working. Once it is running then if you run into throughput
problems that is the time to work out where the bottleneck is and
refactor to improve the situation. As I said above I can virtually
guarantee that you will find that the bottleneck is not where you
initially guessed it would be. The fact that you have a full test
suite means you can happily refactor secure in the knowledge that your
app continues to work.

Colin

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.

Wrong – you can use the :select option.

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.

Just the ones in the form.

When in doubt, watch the generated SQL.

Seems to be very inefficient. I only
want to read and write the values of the form that I need.

That’s all that happens. And Colin is right: you’re trying to
prematurely optimize. Don’t do that.

Best,

Marnen Laibow-Koser
http://www.marnen.org
[email protected]

Sent from my iPhone

yurokle wrote in post #969023:

Why do you want to do it?
You don’t want to give possibility to update some fiends to some
users?
Or you have some fields with a lot of data(BLOB) ?

I basically want to maximize efficiency. I don’t see why I would be
reading and writing additionaly database fields if I don’t have to,
especially for very large tables.

Thanks to all who responded to my query. Pleasantly surprised as a
newbie at the active and passionate rails community.

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.

  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 :slight_smile:

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:inmethod_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 :slight_smile: 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.
  1. 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).

  1. 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:inmethod_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