CODEWITHSUNDEEP
rubypostgresqlsinatraerb
nathas
nathas

Reputation: 949

Ruby w/ Postgres & Sinatra - Query won't order right with parameter?

So I set a variable in my main ruby file that's handling all my post and get requests and then use ERB templates to actually show the pages. I pass the database handler itself into the erb templates, and then run a query in the template to get all (for this example) grants.

In my main ruby file:

grants_main_order = "id_num"
get '/grants' do
    erb :grants, :locals => {:db=>db, :order=>grants_main_order, :message=>params[:message]}
end

In the erb template:

db = locals[:db]
getGrants = db.exec("SELECT * FROM grants ORDER BY $1", [locals[:order]])

This produces some very random ordering, however if I replace the $1 with id_num, it works as it should.

Is this a typing issue? How can I fix this? Using string replacement with #{locals[:order]} also gives funky results.

Upvotes: 0

Views: 927

Answers (3)

Fredrik Ramsberg
Fredrik Ramsberg

Reputation: 11

Parameters are there to put in constant values into the query. It's possible and legal, but not meaningful to use them in an ORDER BY-clause.

Say you want to issue this query:

SELECT first_name, last_name
  FROM people
 ORDER BY first_name

If you put "first_name" in a string and pass it in as a parameter, you instead get:

SELECT first_name, last_name
  FROM people
 ORDER BY "first_name"

The difference is huge. That last ORDER BY-clause really tells te database not to care about the column values for each row, and just sort as if all rows were identical. Sorting order will be random.

Upvotes: 1

gaqzi
gaqzi

Reputation: 3807

have you inspected what locals[:order] is? Maybe something funky in there.

p locals[:order]

Upvotes: 0

Joshua Smith
Joshua Smith

Reputation: 6631

I would recommend using datamapper (http://datamapper.org/) for sinatra. It's a very slick ORM and handles the paramaterized queries you are trying to build quite well.

Upvotes: 0

Related Questions