Running a SQL query in rails

Question

Hello

I have a rails application where I have to subtract 2 time stamps and display the result on the webpage

I have used the following sql statement and I am getting the results.

select (julianday(resolutions.created_at)-julianday(tickets.created_at))*24 from tickets,resolutions

the value of tickets.created_at is 2010-04-05 18:59:02 which is a time stamp and value of resolutions.created_at is 2010-04-08 08:10:33

Now where do I put this sql so that I can be seen in my webpage. I tried it in the views page with the following but nothing showed up:

<% @sql = "select (julianday(resolutions.created_at)-julianday(tickets.created_at))*24 from tickets,resolutions" %>
<% @t=(ActiveRecord::Base.connection.execute(@sql)) %>

So how do I display it on my webpage?

when I perform the above I get the output printed on the webpage as

061.1919444389641(julianday(resolutions.created_at)-julianday(tickets.created_at))*2461.1919444389641

only 61.1919444389641 is supposed to be printed but the query statement is also getting printed.

Answer 1

You should put your custom SQL in your model class, definitely not in the view.

Answer 2

In your controller

@tickets = Tickets.find(
  :all, 
  :joins => :resolution, 
  :select => '(julianday(resolutions.created_at)-julianday(tickets.created_at))*24 AS interval'
)

In your view

<% @tickets.each do |ticket|%>
  <%= ticket.interval %>
<% end %>

Generally you would put this logic in one of your models though, like this:

In your Tickets Model

def time_to_resolve
  resolution.created_at - created_at
end

To reduce the number of queries when iterating over multiple queries you would use this:

Tickets.find(:all, :include => :resolution)