On Selecting A Single Column

July 9, 2011 § 1 Comment

Many times when we are selecting a rows out of the database we just want a single column and have no need for the entire object. There are a number of ways to accomplish this with ActiveRecord. One can get all the records from the database and then collect the attribute needed:

Posts.where(:status => 'published').collect(&:id)
=> [ 1, 5, 8, 10 ]

This has the benefit of being able to us any overwritten accessors, but has a lot of overhead associated with generating the objects. Another way to do it is to go directly to the database:

ActiveRecord::Base.connection.select_values("SELECT id FROM posts WHERE status = 'published'")
 => [ 1, 5, 8, 10 ]

This is much faster, but requires one to use the direct connection to the database and have the SQL literal prepared. Not particularly user friendly even if you can get the SQL literal using the to_sql:

ActiveRecord::Base.connection.select_values(Posts.where(:status => 'published').select(:id).to_sql)
 => [ 1, 5, 8, 10 ]

Wouldn’t it be nicer if you could just do the following:

Post.where(:status => 'published').select_column(:id)
 => [ 1, 5, 8, 10 ]

The select-column gem provides the above functionality above.  You can you it in your Rails 3 app or checkout the source code over on github.

Usage

select_column accepts a single optional argument. This is the column that you want to have returned in an array. The returned column can also be specified using the select query method.

If neither a select nor an argument is given, :id is assumed to be the column to be returned. If multiple select query methods are present, the first one defined will be the column returned.

Some examples:

# selects an array of ids
Post.select_column

# selects an array of titles
Post.select_column(:title)

# selects an array of ids
Post.where(:status => 'published').select_column

# selects an array of titles
Post.where(:status => 'published').select_column(:title)

# selects an array of titles
Post.select(:title).where(:status => 'published').select_column

Update (Jan 21, 2012): It’s like they keep looking at my gems and integrating them into Rails. As of Rails 3.2 this gem’s functionality has been replicated by ActiveRecord::Relation#pluck. Check it out in the release notes.

Advertisements

On Serialized Accessors

June 16, 2011 § 1 Comment

One of the nice things about brand new Rails apps is that all of your database tables are nice, small and manageable.  You have relatively few columns in any given table and you are probably querying on most of the columns at some point in the app.  As your app grows in size and complexity, so do your tables.  A table that once upon a time had 5 or 10 columns now has 20 or 30.  Not unreasonable number, but you are starting accumulate a number of columns that aren’t queried against.  What should we do with these columns?

One option is to just let them build up.  It doesn’t really hurt anything does it?  I am sure that some database experts out there can weigh in on how number of columns affects performance.  Option two is where I’d to show off something a little more interesting.  Check out the data-attributes ruby gem over on github.

The premise is pretty simple.  ActiveRecord allows for easy serialization of objects to a text field in the database. data-attributes makes use of this and adds attributes that read from and written to a serialized hash that is stored in a text field in the database. From the developers perspective, once a data attribute is defined, it can be used just like any other attribute. Validations work just like they do on column based attributes. To use this gem just add the following to your Gemfile in your Rails 3 project.

gem 'data-attributes'

Let’s take a look at it in action.  We start with a user model with a serialized attribute called data.

class User < ActiveRecord::Base
  serialize :data
end

Now, let’s say we have some piece of information that we want to include in the user record, but it isn’t something that we are going to have every query on.  We define a data attribute like so:

data_attribute :favorite_food

Now how do we use this?  We can see the results of adding the above to our Userobject.

u = User.new
u.favorite_food = "watermelon"
puts u.favorite_food
=> watermelon
puts u.data.inspect
=> { "favorite_food" => "watermelon" }

Pretty easy.  The default field that the gem tries to save everything to is data, but that is just based on personal convention.  If you want to change this, it’s as easy as adding the following line to your model:

data_attribute_column :food_preferences

Or if you have multiple serialized attributes and you want to send different data attributes to different serialized attributes you can do the following:

data_attribute :favorite_food, { :serialized_column => :food_preferences }

You can also set the default value to be returned for the data attribute:

data_attribute :favorite_food, { :default => "peanut butter" }

One of the things that makes all of this possible is that fact that ActiveRecord has multiple layers of accessor that happen when you call something like user.name.  What happens is that the method name gets called which in turns calls read_attribute(:name).  Similarly name=(val) calls write_attribute(:name, val).

data-attributes contains similar under-the-hood method read_data_attribute and write_data_attribute.  This way you can have a little more control over the values that are read and written to your object.

Some things to be noted. If your ActiveRecord object has a serialized attribute then that attribute will be saved to database every time you call save.  This is because it doesn’t know if the serialized object has been edited in place or not, so it just writes it to the database every time for good measure.

Update 2011-12-26: ActiveRecord 3.2 now has some of this basic functionality built in by way of ActiveRecord::Store.

Where Am I?

You are currently browsing the ActiveRecord category at The On Blog.

%d bloggers like this: