find_by_sql() goes in model or controller?

rubyonrails-talk
1

What is the canonical way to do a simple GROUP BY using AR? I have a column, I'd like to just get the total for a certain user into the view. So I can do find_by_sql() and then get the first element of the collection in the controller, that's fine.

But it seems like the SQL stuff (assuming there is no way to do it with just find() ) should go in the model. Since the model is in charge of DB stuff, right? So is what is The Correct Way (tm) to do it?

thanks sam

2

I'd defiantly say the model. The best way to think about it is 'could I need this outside of a controller?' - most 'business logic' like this could be used elsewhere in the application. Pretty much anything that isn't dealing directly with something going from or to the view should probably be in the model.

It's also better from a design point of view. A 'user.total' method is far easy to test and re-use.

Steve

3

OK, that's what I thought. But it does contradict my AWDWR book it seems. Even a normal find() with all kinds of :conditions and :joins seems like it couples the controller to the DB too much for my taste

That said, do I just do something like:

class Entry < ActiveRecord::Base    def total_by_day(thedate)         find_by_sql ["SELECT SUM(duration) FROM entries WHERE entry_date = ?", thedate.to_s]    end end

and then in the controller @todays_total = Entry.total_by_day(currdate)

if so, why doesn't it work? :wink:

I get "undefined method `total_by_day' for Entry:Class"

??

4

You need to define it like: class Entry < ActiveRecord::Base def self.total_by_day(thedate); ...; end end

This way it becomes a class method.

Entry.total_by_day() is a method called on the class Entry not the Instance Entry.new().total_by_day()

Hope that helps, Jim

5

It surely did :wink:

I was a little lost on why it did - but on page 34 of the pickaxe I was enlightened

thanks a bunch