gal gal - 6 months ago 30
Ruby Question

Combine Ruby variable in SQL statement rails to find number of days difference between two dates

This is my SQL query:

Battle.all.order("votes_count / #{(DateTime.now.to_i - created_at.to_i) / 86400}")


votes_count
and
created_at
are battle columns; I want to divide
votes_count
by the day difference of
DateTime
now
and
battle
created_at
.
Right now I get a syntax error; how can I make it work? I'm using PostgreSQL in my Rails 4 app.

EDIT

Changed ' to ";

The error I get:

NameError: undefined local variable or method 'created_at' for main:Object


created_at is battle column not ruby variable..

Thanks.

Answer

This should solve your problem :

to_date_str = "to_date('#{DateTime.now.strftime '%d/%m/%Y %H:%M'}', 'DD/MM/YYYY HH24:MI')"

str = "votes_count / (EXTRACT(epoch FROM (#{to_date_str} - created_at))/86400)::int" 

Battle.order(str)

The first problem you had was a syntax error.

Your syntax error is due to the fact that you are using single quotes ' instead of double quotes ".

With the single quote the expression #{(DateTime.now.to_i - created_at.to_i) / 86400} will not be replaced by its value, whereas with double quotes it will be computed and inserted in the string.

So to solve your syntax error use :

Battle.all.order("votes_count / #{(DateTime.now.to_i - created_at.to_i) / 86400}")

But it did not completely solved your problem. You got NameError: undefined local variable or method 'created_at' for main:Object. Why ?

You got this error because you are computing the string "votes_count / #{(DateTime.now.to_i - created_at.to_i) / 86400}" before the order gets executed. So at this point ruby does not know which Battle is being processed. In fact it does not know since NO Battle is being processed at this point. That's why ruby tells you there is no "created_at". The solution is to use SQL instead of trying to do it in ruby.

Comments