CODEWITHSUNDEEP
ruby-on-railsdateselectdatediff
Dabidi
Dabidi

Reputation: 407

select by nearest date

i've a performance problem with this query:

@event = Event.find_by_sql("
    SELECT * FROM `IdeProNew_development`.`events`
    WHERE device_id = #{session[:selected_cam_id]}
    AND data_type = 'image'
    AND created_at BETWEEN CONVERT('#{params[:selected_date].to_time.beginning_of_day}', DATETIME)
    AND CONVERT('#{params[:selected_date].to_time.end_of_day}', DATETIME)
    ORDER BY abs(CONVERT('#{params[:selected_date]}', DATETIME)- created_at) LIMIT 1
").first

i use this to select the nearest event by the "selected_date"... it's ok but it's very slow because scan all table (it's very big) and sort by the difference between the selected date and the creation date of the record.

i try to use DATEDIFF like this:

@event = Event.find_by_sql("
    SELECT * FROM `IdeProNew_development`.`events`
    WHERE device_id = #{session[:selected_cam_id]}
    AND data_type = 'image' AND created_at
    BETWEEN CONVERT('#{params[:selected_date].to_time.beginning_of_day}', DATETIME)
    AND CONVERT('#{params[:selected_date].to_time.end_of_day}', DATETIME)
    ORDER BY abs(DATEDIFF(CONVERT('#{params[:selected_date]}', DATETIME), created_at)) LIMIT 1
").first`

but it's not work very well (sometimes give me a wrong result) and it's slow too.

where is my mistake? could i use some type of indexing to make this query fast?

Upvotes: 1

Views: 1172

Answers (2)

Mukesh
Mukesh

Reputation: 1267

You can Also try this 

     @event = Event.where(:device_id => session[:selected_cam_id])
     .where(:data_type => 'image').to_a
     .select{|i| i.created_at.to_time >= params[:selected_date].to_time.beginning_of_day 
&& i.created_at.to_time <= params[:selected_date].to_time.end_of_day}
.sort{ |x,y| y.created_at  <=> x.created_at}.first

Upvotes: 1

CupraR_On_Rails
CupraR_On_Rails

Reputation: 2489

Why don't you use active record to do this rather than an SQL query? Something like this :

`@event = Event.where(:device_id => session[:selected_cam_id]).
               where(:data_type => 'image').
               where("created_at >= ? AND created_at <= ?",    
                                 params[:selected_date].to_time.beginning_of_day, 
                                 params[:selected_date].to_time.end_of_day).
               order("created_at DESC").first`

I think it's more efficient.

Upvotes: 1

Related Questions