Forum: Ruby on Rails use of SET command in find_by_sql

Announcement (2017-05-07): www.ruby-forum.com is now read-only since I unfortunately do not have the time to support and maintain the forum any more. Please see rubyonrails.org/community and ruby-lang.org/en/community for other Rails- und Ruby-related community platforms.
E98de78bb42013c488fe8aa9d77d2cb1?d=identicon&s=25 Steve Odom (Guest)
on 2005-12-21 17:53
(Received via mailing list)
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
F16def8015a7562a285c65a14be48540?d=identicon&s=25 Mathieu Chappuis (Guest)
on 2005-12-21 17:59
(Received via mailing list)
> 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
E98de78bb42013c488fe8aa9d77d2cb1?d=identicon&s=25 Steve Odom (Guest)
on 2005-12-21 18:08
(Received via mailing list)
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
A2c85dc5ee81b12e3cc0a6522e8d079d?d=identicon&s=25 Chris Hall (Guest)
on 2005-12-21 18:35
(Received via mailing list)
i could be wrong, but i think the problem lies in the fact that you are
trying to execute mulitple queries.
E98de78bb42013c488fe8aa9d77d2cb1?d=identicon&s=25 Steve Odom (Guest)
on 2005-12-21 19:32
(Received via mailing list)
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
89441a6c74647d292f5cc951eae24cfa?d=identicon&s=25 Jack Christensen (Guest)
on 2005-12-21 19:47
(Received via mailing list)
_______________________________________________
Rails mailing list
Rails@lists.rubyonrails.org
http://lists.rubyonrails.org/mailman/listinfo/rails
A2c85dc5ee81b12e3cc0a6522e8d079d?d=identicon&s=25 Chris Hall (Guest)
on 2005-12-21 19:51
(Received via mailing list)
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
E98de78bb42013c488fe8aa9d77d2cb1?d=identicon&s=25 Steve Odom (Guest)
on 2005-12-21 20:30
(Received via mailing list)
That worked, Chris.

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

Thanks.

Steve
This topic is locked and can not be replied to.