gal gal - 1 year ago 69
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 / #{( - created_at.to_i) / 86400}")

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


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..


Answer Source

This should solve your problem :

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

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


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 #{( - 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 / #{( - 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 / #{( - 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.