CODEWITHSUNDEEP
ruby-on-railsrubysql-server
Phil
Phil

Reputation: 815

How to convert SQL query to ActiveRecord

Background:

Writing a Ruby on Rails app that is connect to MS SQL DB(don't ask why, its something I cant get around), the DB is quiet large and can have up to 2/3 million rows.

There is one main columns that matter for me at the moment and that is 'TimeUTC' and the table is called ApplicationLog and the query will be in the ApplicationLogController.

Problem:

I want to write a query that i can pass in two dates and it will group all the records by day using the 'TimeUTC' column and gives me a total of all the records for those days in between those two dates.

I have the SQL Query:

DECLARE @StartDate DateTime = '2014-01-04 00:00:00' 
DECLARE @EndDate DateTime = '2014-02-04 23:59:59' 

select (dateadd(DAY,0, datediff(day,0, TimeUtc))) as [DATE], count(*) 
from applicationlog (nolock)
where TimeUtc between @StartDate and  @EndDate 
group by dateadd(DAY,0, datediff(day,0, TimeUtc))
order by [DATE] desc

I try starting with something like this:

@results = ApplicationLog.select((dateadd(DAY,0, datediff(day,0, TimeUtc)) as [date], count(*)).
           group(dateadd(DAY,0, datediff(day,0, TimeUtc))).order(date desc)

Now I am a newbie at this so I could be so far off the track its not funny, but any help would be great. Am I even going about this the right way, is there a better way??

Upvotes: 2

Views: 1171

Answers (1)

carpamon
carpamon

Reputation: 6623

Try with the following code, which uses Arel code with some SQL embedded. 

class ApplicationLog < ActiveRecord::Base

  def self.between(range)
    columns = Arel.sql('dateadd(DAY,0,datediff(day,0,TimeUtc)) as date, COUNT(*)')
    conditions = arel_table['TimeUTC'].in(range)
    query = arel_table.project(columns).where(conditions).group([1]).order('date desc')
    ActiveRecord::Base.connection.execute(query.to_sql)
  end
end

Then use ApplicationLog.between(1.week.ago..Time.now).

Upvotes: 1

Related Questions