Forum: Ruby on Rails Getting distinct years from a date column the Rails way

Announcement (2017-05-07): www.ruby-forum.com is now read-only since I unfortunately do not have the time to support and maintain the forum any more. Please see rubyonrails.org/community and ruby-lang.org/en/community for other Rails- und Ruby-related community platforms.
F0a898f6a6131fe68d79ba1a6c060dd9?d=identicon&s=25 Jon Bauman (baumanj)
on 2006-04-11 08:02
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?
6dab365a82517fb694650a57ee88e4a4?d=identicon&s=25 joey__ (Guest)
on 2006-04-11 09:16
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
F0a898f6a6131fe68d79ba1a6c060dd9?d=identicon&s=25 Jon Bauman (baumanj)
on 2006-04-11 13:46
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
This topic is locked and can not be replied to.