Getting distinct years from a date column the Rails way

Rails
1

I have an original_at column in my photos table. I’d like to get a list
of distinct years that occur in this column, and some of the values are
NULL. I see two ways of doing this. The SQLish way:

photos = Photo.find :all, :select => ‘distinct year(original_at)’

This is efficient in that it utilizes the database to do the filtering,
and the code is not too ugly. The big downside is that I have to access
the results like this:

@years = photos.map {|p| p[‘year(original_at)’] }

Then, there’s the ActiveRecord way (as best I can figure it):

@years = Photo.find(:all).map {|p| p.original_at && p.original_at.year
}.uniq

This gives a nice array of the years, but it’s sorta ugly, and it’s
using model code for things the database is good at (filtering results).

Can anyone suggest a better or more idiomatic approach to this? It seems
there should be support for finders of a certain column that return an
array of objects of the column’s type rather than the model type.
Something like:

@years = Photo.find_column, :select => ‘distinct year(original_at)’

Does that seem useful, or is there a way to do that cleanly now?

2

Jon B. wrote:

I have an original_at column in my photos table. I’d like to get a list
of distinct years that occur in this column, and some of the values are
NULL. I see two ways of doing this. The SQLish way:

photos = Photo.find :all, :select => ‘distinct year(original_at)’

This is efficient in that it utilizes the database to do the filtering,
and the code is not too ugly. The big downside is that I have to access
the results like this:

@years = photos.map {|p| p[‘year(original_at)’] }

Then, there’s the ActiveRecord way (as best I can figure it):

@years = Photo.find(:all).map {|p| p.original_at && p.original_at.year
}.uniq

This gives a nice array of the years, but it’s sorta ugly, and it’s
using model code for things the database is good at (filtering results).

Can anyone suggest a better or more idiomatic approach to this? It seems
there should be support for finders of a certain column that return an
array of objects of the column’s type rather than the model type.
Something like:

@years = Photo.find_column, :select => ‘distinct year(original_at)’

Does that seem useful, or is there a way to do that cleanly now?

Maybe:
photos = Photo.find :all, :select => ‘distinct year(original_at) as
orginial_year’
I’m no SQL wizard!
j`ey

3

Thanks.

Now I have this, which is the best to far:

@years = Photo.find(:all, :select => ‘distinct year(original_at) as
year’).map(&:year)

This is better than my earlier attempts, but I still think it would be
nice if there were a way to do finds that returned an array of
attribute/column-specific objects as opposed to ActiveRecord objects. If
one were dealing wit a very large result (say, distinct fist names in a
huge list of users) the overhead of the map call might be annoying.
Plus, the results that one gets from the actual find method (for
example):

[#<Photo:0x23387ec @attributes={“year”=>“2003”}>, #<Photo:0x23386fc
@attributes={“year”=>“2002”}>, #<Photo:0x23385e4
@attributes={“year”=>nil}>]

Don’t really make sense. These shouldn’t be Photo objects, since they
don’t really represent photos. We already have
ActiveRecord::Base.count_by_sql, so why not something for this?

Thanks again for the suggestion. Your SQL magic is greater than mine.

joey__ wrote:

Jon B. wrote:

I have an original_at column in my photos table. I’d like to get a list
of distinct years that occur in this column, and some of the values are
NULL. I see two ways of doing this. The SQLish way:

photos = Photo.find :all, :select => ‘distinct year(original_at)’

This is efficient in that it utilizes the database to do the filtering,
and the code is not too ugly. The big downside is that I have to access
the results like this:

@years = photos.map {|p| p[‘year(original_at)’] }

Then, there’s the ActiveRecord way (as best I can figure it):

@years = Photo.find(:all).map {|p| p.original_at && p.original_at.year
}.uniq

This gives a nice array of the years, but it’s sorta ugly, and it’s
using model code for things the database is good at (filtering results).

Can anyone suggest a better or more idiomatic approach to this? It seems
there should be support for finders of a certain column that return an
array of objects of the column’s type rather than the model type.
Something like:

@years = Photo.find_column, :select => ‘distinct year(original_at)’

Does that seem useful, or is there a way to do that cleanly now?

Maybe:
photos = Photo.find :all, :select => ‘distinct year(original_at) as
orginial_year’
I’m no SQL wizard!
j`ey