On Feb 25, 2009, at 11:37 AM, Maurício Linhares wrote:
> Ok, so this is the best time to start learning SQL, here's a good
> start -> http://oreilly.com/catalog/9780596526849/
>
> Here's how the query would look like:
>
> SELECT departure, COUNT(*) AS departures_count FROM bookings GROUP
> BY departure
>
> -
> Maurício Linhares
> http://alinhavado.wordpress.com/ (pt-br) | http://
> blog.codevader.com/ (en)
You can do some of this with ActiveRecord, too. (Note this is my own
little extension so don't go looking in ActiveRecord documentation for
this particular module.)
module ActiveRecord
module Groups
def groups(*columns)
return if columns.empty?
options = columns.last.is_a?(Hash) ? columns.pop.dup : {}
select = columns.join(',') + ',count(*) AS count_all'
group = columns.join(',')
columns = (columns + ['count_all']).map{|c|c.to_s}
find(:all, :select => select, :conditions =>
options.delete(:conditions),
:group => group).map {|rec| rec.attributes(:only =>
columns).values_at(*columns) }
end
end
end
require 'pp'
pp Booking.count # so the Booking model class will exist
class Booking
extend ActiveRecord::Groups
end
cols = [ :departure ]
result = Booking.groups(*cols)
# which gives something like
#=> [ ["AMS", 3 ], ["BCN", 1], ["BKK", 2] ]
# or write that out to a CSV file
require 'fastercsv'
filename = 'counts.csv'
FasterCSV.open(filename, 'w') do |csv|
csv << cols
result.each {|r| csv << r }
end
puts "%5d rows in %s"%[result.size, filename]
Exercises for the reader (the OP, in particular):
* look at how the :select option can limit the attributes created in
the model
* read how the :group option works on ActiveRecord::Base.find
* look at the #attributes method of a model and the Hash#values_at
method
-Rob
Rob Biedenharn http://agileconsultingllc.com
[email protected]
> On Wed, Feb 25, 2009 at 1:32 PM, Remco Swoany
> <[email protected]> wrote:
>>
>> Maurício Linhares wrote:
>>> Why don't you do it at the database with an order by?
>>>
>>> -
>>> Maurício Linhares
>>> http://alinhavado.wordpress.com/ (pt-br) | http://
>>> blog.codevader.com/
>>> (en)
>>>
>>>
>>>
>>> On Wed, Feb 25, 2009 at 1:04 PM, Remco Swoany
>>
>> hmm...i don't now how to count the values through the order_by
>> condition
>> (yep..i am newbie)
>>
>> grtz..remco
>> --
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups "Ruby
on Rails: Talk" group.
To post to this group, send email to [email protected]
To unsubscribe from this group, send email to
[email protected]
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---
