# Find\_by\_sql() goes in model or controller?

**URL:** <https://www.ruby-forum.com/t/find-by-sql-goes-in-model-or-controller/93170>\
**Category:** Rails\
**Created:** [April 28, 2007, 5:55pm UTC](https://www.ruby-forum.com/t/find-by-sql-goes-in-model-or-controller/93170 "2007-04-28T17:55:31Z")\
**Posts on this page:** 5\
**Page:** 1

<div class="post-metadata">

**Author:** ![sbeam](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/s/258eb7/32.png) [@sbeam](https://www.ruby-forum.com/u/sbeam)\
**Post date:** [April 28, 2007, 5:55pm UTC](https://www.ruby-forum.com/t/find-by-sql-goes-in-model-or-controller/93170/1 "2007-04-28T17:55:31Z")

</div>

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 ™ to do  
it?

thanks  
sam

---

<div class="post-metadata">

**Author:** ![sbeam](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/s/258eb7/32.png) [@sbeam](https://www.ruby-forum.com/u/sbeam)\
**Post date:** [April 28, 2007, 6:06pm UTC](https://www.ruby-forum.com/t/find-by-sql-goes-in-model-or-controller/93170/2 "2007-04-28T18:06:32Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![sbeam](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/s/258eb7/32.png) [@sbeam](https://www.ruby-forum.com/u/sbeam)\
**Post date:** [April 28, 2007, 6:31pm UTC](https://www.ruby-forum.com/t/find-by-sql-goes-in-model-or-controller/93170/3 "2007-04-28T18:31:34Z")

</div>

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? 😉

I get “undefined method `total\_by\_day’ for Entry:Class”

??

---

<div class="post-metadata">

**Author:** ![sbeam](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/s/258eb7/32.png) [@sbeam](https://www.ruby-forum.com/u/sbeam)\
**Post date:** [April 28, 2007, 8:07pm UTC](https://www.ruby-forum.com/t/find-by-sql-goes-in-model-or-controller/93170/4 "2007-04-28T20:07:40Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![sbeam](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/s/258eb7/32.png) [@sbeam](https://www.ruby-forum.com/u/sbeam)\
**Post date:** [April 29, 2007, 1:50am UTC](https://www.ruby-forum.com/t/find-by-sql-goes-in-model-or-controller/93170/5 "2007-04-29T01:50:01Z")

</div>

On Apr 28, 2:06 pm, removed\_email\_address@domain.invalid wrote:

> Hope that helps,  
> Jim

It surely did 😉

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

thanks a bunch