thr3ads.net - Rails - use of SET command in find_by

If this information is useful, please help other people find it:
Share via:

Steve Odom

2005-Dec-21 16:50 UTC

use of SET command in find_by_sql

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


_______________________________________________
Rails mailing list
Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org
http://lists.rubyonrails.org/mailman/listinfo/rails

Mathieu Chappuis

2005-Dec-21 16:58 UTC

head link

Re: use of SET command in find_by_sql

> 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

Steve Odom

2005-Dec-21 17:05 UTC

head link

Re: use of SET command in find_by_sql

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

On 12/21/05, Mathieu Chappuis
<mathieu.chappuis.lists-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org>
wrote:>
> > 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
> _______________________________________________
> Rails mailing list
> Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org
> http://lists.rubyonrails.org/mailman/listinfo/rails
>

_______________________________________________
Rails mailing list
Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org
http://lists.rubyonrails.org/mailman/listinfo/rails

Chris Hall

2005-Dec-21 17:32 UTC

head link

Re: use of SET command in find_by_sql

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

On 12/21/05, Steve Odom
<steve.odom-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org>
wrote:>
> 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
>
> On 12/21/05, Mathieu Chappuis
<mathieu.chappuis.lists-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote:
> >
> > > 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
> > _______________________________________________
> > Rails mailing list
> > Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org
> > http://lists.rubyonrails.org/mailman/listinfo/rails
> >
>
>
> _______________________________________________
> Rails mailing list
> Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org
> http://lists.rubyonrails.org/mailman/listinfo/rails
>
>
>

_______________________________________________
Rails mailing list
Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org
http://lists.rubyonrails.org/mailman/listinfo/rails

Steve Odom

2005-Dec-21 18:32 UTC

head link

Re: use of SET command in find_by_sql

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

On 12/21/05, Chris Hall
<christopher.k.hall-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org>
wrote:>
> i could be wrong, but i think the problem lies in the fact that you are
> trying to execute mulitple queries.
>
> On 12/21/05, Steve Odom <
steve.odom-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote:
>
> > 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
> >
> > On 12/21/05, Mathieu Chappuis
<mathieu.chappuis.lists-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote:
> > >
> > > > 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
> > > _______________________________________________
> > > Rails mailing list
> > > Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org
> > > http://lists.rubyonrails.org/mailman/listinfo/rails
> > >
> >
> >
> > _______________________________________________
> > Rails mailing list
> > Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org
> > http://lists.rubyonrails.org/mailman/listinfo/rails
> >
> >
> >
>
> _______________________________________________
> Rails mailing list
> Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org
> http://lists.rubyonrails.org/mailman/listinfo/rails
>
>
>

_______________________________________________
Rails mailing list
Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org
http://lists.rubyonrails.org/mailman/listinfo/rails

Jack Christensen

2005-Dec-21 18:46 UTC

head link

Re: use of SET command in find_by_sql

As was mentioned earlier you are trying to execute multiple statements
at once. While MySQL allows this, I have not been able to get this to
work. Try executing one statement at a time.

Jack Christensen

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

  def
self.sql_for_rankings()

      res = ActiveRecord::Base.connection.select_one &lt;&lt;-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 &lt;&lt; "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

  On 12/21/05, Chris Hall
&lt;christopher.k.hall-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org&gt;
wrote:
  i
could be wrong, but i think the problem lies in the fact that you are
trying to execute mulitple queries.

    On 12/21/05, 
Steve Odom &lt;
steve.odom-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org&gt; wrote:

      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
&lt;&lt;-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

      On 12/21/05, Mathieu Chappuis
      
&lt;mathieu.chappuis.lists-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org&gt;
wrote:
      &gt;
I''m trying to execute something like this method in a model:

&gt;

&gt;     def self.sql_for_rankings()

&gt;         sql =  "SET @counter:=0;"

&gt;         sql &lt;&lt; " SET @counter:=0;

&gt;                    
SELECT *, @counter:=@counter+1 AS rank

&gt;                    
FROM testscores. "

&gt;         find_by_sql(sql)

&gt;     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

_______________________________________________

Rails mailing list

        Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org

        http://lists.rubyonrails.org/mailman/listinfo/rails

_______________________________________________

Rails mailing list

      Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org

      http://lists.rubyonrails.org/mailman/listinfo/rails

_______________________________________________

Rails mailing list

    Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org

    http://lists.rubyonrails.org/mailman/listinfo/rails

_______________________________________________
Rails mailing list
Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org
http://lists.rubyonrails.org/mailman/listinfo/rails

-- 
Jack Christensen
jackc-/SOt/BrQZzOj3I+7jmQ39gC/G2K4zDHf@public.gmane.org


_______________________________________________
Rails mailing list
Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org
http://lists.rubyonrails.org/mailman/listinfo/rails

Chris Hall

2005-Dec-21 18:49 UTC

head link

Re: use of SET command in find_by_sql

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

On 12/21/05, Steve Odom
<steve.odom-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org>
wrote:>
> 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
>
> On 12/21/05, Chris Hall
<christopher.k.hall-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote:
>
> > i could be wrong, but i think the problem lies in the fact that you
are
> > trying to execute mulitple queries.
> >
> > On 12/21/05, Steve Odom <
steve.odom-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote:
> >
> > > 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
> > >
> > > On 12/21/05, Mathieu Chappuis
<mathieu.chappuis.lists-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org>
> > > wrote:
> > > >
> > > > > 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
> > > > _______________________________________________
> > > > Rails mailing list
> > > > Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org
> > > > http://lists.rubyonrails.org/mailman/listinfo/rails
> > > >
> > >
> > >
> > > _______________________________________________
> > > Rails mailing list
> > > Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org
> > > http://lists.rubyonrails.org/mailman/listinfo/rails
> > >
> > >
> > >
> >
> > _______________________________________________
> > Rails mailing list
> > Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org
> > http://lists.rubyonrails.org/mailman/listinfo/rails
> >
> >
> >
>
> _______________________________________________
> Rails mailing list
> Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org
> http://lists.rubyonrails.org/mailman/listinfo/rails
>
>
>

_______________________________________________
Rails mailing list
Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org
http://lists.rubyonrails.org/mailman/listinfo/rails

Steve Odom

2005-Dec-21 19:28 UTC

head link

Re: use of SET command in find_by_sql

That worked, Chris.

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

Thanks.

Steve

On 12/21/05, Chris Hall
<christopher.k.hall-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org>
wrote:>
> 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
>
> On 12/21/05, Steve Odom
<steve.odom-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote:
>
> > 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
> >
> > On 12/21/05, Chris Hall <
christopher.k.hall-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote:
> >
> > > i could be wrong, but i think the problem lies in the fact that
you
> > > are trying to execute mulitple queries.
> > >
> > > On 12/21/05, Steve Odom <
steve.odom-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote:
> > >
> > > > 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
> > > >
> > > > On 12/21/05, Mathieu Chappuis
<mathieu.chappuis.lists-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org>
> > > > wrote:
> > > > >
> > > > > > 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
> > > > > _______________________________________________
> > > > > Rails mailing list
> > > > > Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org
> > > > > http://lists.rubyonrails.org/mailman/listinfo/rails
> > > > >
> > > >
> > > >
> > > > _______________________________________________
> > > > Rails mailing list
> > > > Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org
> > > > http://lists.rubyonrails.org/mailman/listinfo/rails
> > > >
> > > >
> > > >
> > >
> > > _______________________________________________
> > > Rails mailing list
> > > Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org
> > > http://lists.rubyonrails.org/mailman/listinfo/rails
> > >
> > >
> > >
> >
> > _______________________________________________
> > Rails mailing list
> > Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org
> > http://lists.rubyonrails.org/mailman/listinfo/rails
> >
> >
> >
>
> _______________________________________________
> Rails mailing list
> Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org
> http://lists.rubyonrails.org/mailman/listinfo/rails
>
>
>

_______________________________________________
Rails mailing list
Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org
http://lists.rubyonrails.org/mailman/listinfo/rails

Apparently Analagous Threads

Search for more apparently analagous threads

Rails - Dec 2005 - use of SET command in find_by_sql

use of SET command in find_by_sql

Re: use of SET command in find_by_sql

Re: use of SET command in find_by_sql

Re: use of SET command in find_by_sql

Re: use of SET command in find_by_sql

Re: use of SET command in find_by_sql

Re: use of SET command in find_by_sql

Re: use of SET command in find_by_sql

Apparently Analagous Threads