I am trying to figure out how to add columns to a database table from
within a controller. I have a client who wants to be able to add
their own attributes to a model from within the running application,
so I don’t think using migrations is an option.
Basically, I have a Person model which starts off with some basic
attributes (like first_name and last_name), and my client wants to be
able to add new attributes on the fly through the application (like
birthdate or hometown, or anything he can think of). My plan for
implementing this is to use another model (PersonField) to keep track
of each new attribute’s name, label, data type, and default value, and
then tie the creation, updating, or destruction of these PersonField
objects to the addition, changing, or removing of columns to the
‘people’ table in the database.
To restate it in another way, I’m looking for the equivalent to
‘add_column’ in a migration, but I’d like to call it from a
controller.
I have not been able to find a solution to this problem, but I’m
relatively new to Rails (just a few months experience), so I may not
be looking in the right places.
I’d appreciate any help or suggestions anyone can give me. Thanks for
reading this!
On Wed, Jul 30, 2008 at 3:34 AM, mikebind [email protected] wrote:
implementing this is to use another model (PersonField) to keep track
of each new attribute’s name, label, data type, and default value, and
then tie the creation, updating, or destruction of these PersonField
objects to the addition, changing, or removing of columns to the
‘people’ table in the database.
To restate it in another way, I’m looking for the equivalent to
‘add_column’ in a migration, but I’d like to call it from a
controller.
You can always execute arbitrary sql commands by using Model.find_by_sql
A better way may be to store these customs fields in the database:
Person
has_many :person_fields
PersonField
belongs_to :custom_field
belongs_to :person
CustomField
has_many :person_fields
Looking around some more, I found this thread
http://groups.google.com/group/rubyonrails-talk/browse_thread/thread/bfb700028cf5e696#
, where the poster was trying to do something similar. Generally, he
was advised that this was a bad idea and might be better addressed in
other ways, however, these other possible ways weren’t discussed. I’m
not at all committed to implementing this feature (custom information
fields added on the fly from the application) in the way I outlined
above; if there’s a better way to handle it, that would be fine.
Basically, what my client wants is to be able to add new information
fields to records about people. For example, he would like to be able
to add a field for ‘Home Town’ to a Person record. Then, whenever he
creates or edits a Person, he wants there to be a place to enter ‘Home
Town’ information. Obviously this would be easy to do with a
migration (just add a ‘home_town’ column to the ‘people’ table in the
database), but he wants to be able to do this from within the running
application.
It seems most natural to me to alter the database table structure, but
apparently this is a bad idea because of caching that Rails does for
you to improve performance. It sounds as though you can turn off
model caching, but that this typically causes a significant
performance hit. Is there a way to turn it off for just one model?
In my case, the Person model is the only one which would need to be
dynamically altered.
Thanks for any help or suggestions!
-Mike Bindschadler
Thanks for the suggestions! I’ll chew on these some more when it’s
not 2am, but I think the idea from Miles G. sounds very
promising. The bit about a column for each data type really helped
the idea to begin to crystalize for me. Christopher Kintner’s
solution may be similar, but I don’t see yet how having the
CustomField model would help.
I’ll post back when I’ve settled on something and have it working or
if I hit another wall. Thanks again!
-Mike
The reason there’s no easy method for doing what you want to do is
because it’s a terrible idea! Letting the customer arbitrarily change
the structure of the database is guaranteed to get you an unstable
database.
You already have a second table in mind to store data about the
arbitrary fields the customer wants to create. Have a third table that
stores the data and includes pointers back to the attribute table and
the persons table. You can set this sort of thing up easily with a
has_many :through relationship. This is much more stable and requires
no special programming. The customer can create or delete data fields
on the fly, use them inconsistently or not at all, and the data remain
easily accessible.
Thanks for all the help! I ended up doing a slight variation on the
solutions suggested here. The client wanted every Person to have the
same fields rather than adding single custom fields to an individual
person, so I dropped the has_many :people_fields from the Person model
(since all people have all PeopleFields). In outline it looks
something like this:
class Person < ActiveRecord::Base
has_many :custom_people_field_values
class CustomPeopleField < ActiveRecord::Base
has_many :custom_people_field_values
class CustomPeopleFieldValue < ActiveRecord::Base
belongs_to :person
belongs_to :custom_people_field
In the custom_people_fields table there are columns for the field
name, label, data type, default value, and order of appearance. In
the custom_people_field_values table there are id columns for person
and custom people field, as well as columns for data type and one
column for each allowed data type (to hold the appropriate value in
the appropriate format, as suggested by Miles G. above).
I wanted to be able to refer to custom fields just like regular
fields, so if ‘hometown’ was a custom-created field, I wanted to be
able to do things like this
dave = Person.new
dave.hometown = ‘Seattle’
current_town = dave.hometown
To set this up, I added a method_missing method to the Person model
which looks something like this
def method_missing(m, *args)
if
if
# create a new or update a current CustomPeopleFieldValue which
points to the current Person and the matching CustomPeopleField with
the requested value
else # there’s not an = on the end of the method call
if
# return the existing value
else
# return the default value (or nil if no default value) for
this CustomPeopleField
end
end
else # there was no matching field, just treat it like a regular
missing method
super
end
Hope this helps someone else in similar predicament! And thanks again
to everyone who responded!
Very good idea.
I was searching some solution exactly like this.
Thanks for the detailed suggestion!
You could run the execute method and pass it the query that creates the
column
I’d personally create a table to hold these attributes, and add rows to
it,
rather than creating/dropping columns dynamically. That is I’d create
an
attribute model and give it a foreign key to the row in the other table,
the
name, and the value (and possibly a column for each possible type + a
type
column.)