Rails - Apr 2006 - Getting distinct years from a date column the Rails way

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?

-- 
Posted via http://www.ruby-forum.com/.

Jon Bauman 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


-- 
Posted via http://www.ruby-forum.com/.

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 Bauman 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
-- 
Posted via http://www.ruby-forum.com/.