Use of SET command in find_by_sql

Rails
1

Hi,

I’m trying to execute something like this method in a model:

def self.sql_for_rankings()
    sql =  "SET @counter:=0;"
    sql << " SET @counter:=0;
                SELECT *, @counter:=@counter+1 AS rank
                FROM testscores. "
    find_by_sql(sql)
end

Where the new rank column is a kind of autoincrementer inside of this
query.
Ignore for the moment that I can add this counter in ruby rather than in
sql

  • but I am trying to do it in mysql. This executes in mysql with no
    problem
    and returns something like:

|testscore; name, rank|
|85, steve, 2|
|92, tom, 1|

But when I try to run it in rails, it doesn’t like the SET command. It
complains:

You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use
near ';SELECT @counter:=@counter+1 AS rank, p.user_id, p.user_name,
p.delta, p.profit
’ at line 1: SET @counter:=0;SELECT *, @counter:=@counter+1 AS rank
FROM testscores

Can rails not handle this?

Thanks,

Steve
http://www.smarkets.net

2

I’m trying to execute something like this method in a model:

def self.sql_for_rankings()
    sql =  "SET @counter:=0;"
    sql << " SET @counter:=0;
                SELECT *, @counter:=@counter+1 AS rank
                FROM testscores. "
    find_by_sql(sql)
end

Why not just :

 def self.sql_for_rankings()
     sql = " SET @counter:=0;
                 SELECT *, @counter:=@counter+1 AS rank
                 FROM testscores. "
     find_by_sql(sql)
 end
3

Yep, I’ve tried that - same error message. I started with that same
formating then moved to combining the strings thinking it was getting
hung
up on the ; after :=0 in the first line.

I’ve also tried to not use find_by_sql and instead tried something like:
def self.sql_for_rankings()
res = ActiveRecord::Base.connection.select_one <<-EOL
SET @counter:=0;
SELECT *, SELECT *, @counter:=@counter+1 AS rank
FROM testscores
EOL
end

But it always gets hung up on the SET line. (though it works if I run it
directly in mysql.

Steve

4

i could be wrong, but i think the problem lies in the fact that you are
trying to execute mulitple queries.

5

Oh sorry. Those were a cut and paste error. It should read:

def self.sql_for_rankings()
res = ActiveRecord::Base.connection.select_one <<-EOL
SET @counter:=0;
SELECT *, @counter:=@counter+1 AS rank
FROM testscores
EOL
end

And my original should read:

def self.sql_for_rankings()
sql = “SET @counter:=0;”
sql << "SELECT *, @counter:=@counter+1 AS rank
FROM testscores. "
find_by_sql(sql)
end

Thanks.There should only be one select and one set. But it still returns
an
error. And like I said, it works in mysql directly but not in rails.

Steve

6

Rails mailing list
[email protected]
http://lists.rubyonrails.org/mailman/listinfo/rails

7

as I said, i don’t think you can chain queries together like that in
rails.

try something like

class testscore < ActiveRecord::Base
def self.sql_for_rankings
begin
connection.execute(“SET @counter:=0”)
find_by_sql(“select *, @counter:=@counter+1 AS rank FROM
testscores”)
rescue NotImplementedError
# execute not implemented
end
end
end

8

That worked, Chris.

I was not associating a SET statement as a query, but I guess that is
what
it is.

Thanks.

Steve