thr3ads.net - Rails - [Rails] ad-hoc query [May 2006]

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

Jeff Cole

2006-May-23 14:39 UTC

[Rails] ad-hoc query

How do you run an ad-hoc query that does not belong in any model?  I 
thought it was something like ActiveRecord::connection.find("my 
query..."), but I can''t seem to find the documentation on it.  I
know
I''ve seen it somewhere before, though.

thanks,
Jeff

-- 
Posted via http://www.ruby-forum.com/.

Chang Sau Sheong

2006-May-23 16:04 UTC

head link

[Rails] ad-hoc query

It''s ''find_by_sql'', you can do a search on it at
api.rubyonrails.com


Jeff Cole wrote:> How do you run an ad-hoc query that does not belong in any model?  I 
> thought it was something like ActiveRecord::connection.find("my 
> query..."), but I can''t seem to find the documentation on it.
I know
> I''ve seen it somewhere before, though.
>
> thanks,
> Jeff
>
>   

-- 
Sau Sheong

http://blog.saush.com - brain dump
http://www.projectible.com - online project publishing
http://jaccal.sourceforge.net - smart card toolkit

Jack Christensen

2006-May-23 18:31 UTC

head link

Re: ad-hoc query

Chang Sau Sheong wrote:

It''s ''find_by_sql'', you can do a search on it at
api.rubyonrails.com

Jeff Cole wrote:

How do you run an ad-hoc query that does not belong in any model?  I 
thought it was something like ActiveRecord::connection.find("my 
query..."), but I can''t seem to find the documentation on it.  I
know
I''ve seen it somewhere before, though.

thanks,
Jeff

You can also use ActiveRecord::Base.connection "SELECT..."

This will return an array of hashes with the result set. 

-- 
Jack Christensen
jackc-/SOt/BrQZzMOf2zXYvRtkodd74u8MsAO@public.gmane.org


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

cies

2006-May-23 22:44 UTC

head link

[Rails] ad-hoc query

i did it like this:

ActiveRecord::Base.connection.execute("SELECT SUM(minutes) FROM
declarations WHERE task_id IN (#{task_ids_str})").fetch_row.first.to_i


anyone: if i would like to put a little entry in the active recode
documentation about this, how should i do that?


Cies Breijs.

On 5/23/06, Jack Christensen <jackc@hylesanderson.edu>
wrote:>
>  Chang Sau Sheong wrote:
>  It''s ''find_by_sql'', you can do a search on it
at api.rubyonrails.com
>
>
> Jeff Cole wrote:
>
>
>  How do you run an ad-hoc query that does not belong in any model? I
> thought it was something like ActiveRecord::connection.find("my
> query..."), but I can''t seem to find the documentation on it.
I know
> I''ve seen it somewhere before, though.
>
> thanks,
> Jeff
>
>
>
>
>
>  You can also use ActiveRecord::Base.connection "SELECT..."
>  This will return an array of hashes with the result set.
>
>  --
> Jack Christensen
> jackc@hylesanderson.edu
>
> _______________________________________________
> Rails mailing list
> Rails@lists.rubyonrails.org
> http://lists.rubyonrails.org/mailman/listinfo/rails
>
>
>

-- 
"Computer games don''t affect kids; I mean if Pac-Man affected us
as
kids, we''d all be running around in darkened rooms, munching magic
pills and listening to repetitive electronic music." -- Kristian
Wilson (Nintendo, Inc), 1989

Jeremy Kemper

2006-May-23 22:56 UTC

head link

[Rails] ad-hoc query

On May 23, 2006, at 3:43 PM, cies wrote:> ActiveRecord::Base.connection.execute("SELECT SUM(minutes) FROM
> declarations WHERE task_id IN (#{task_ids_str})").fetch_row.first.to_i
Hey, how about:
   Declaration.sum(:minutes, :conditions => [''task_id in
(?)'',
task_ids])

You can also do things like
   task.declarations.average(:minutes)

jeremy

Jeremy Kemper

2006-May-23 23:02 UTC

head link

[Rails] ad-hoc query

On May 23, 2006, at 3:55 PM, Jeremy Kemper wrote:> On May 23, 2006, at 3:43 PM, cies wrote:
>> ActiveRecord::Base.connection.execute("SELECT SUM(minutes) FROM
>> declarations WHERE task_id IN (# 
>> {task_ids_str})").fetch_row.first.to_i
>
> Hey, how about:
>   Declaration.sum(:minutes, :conditions => [''task_id in
(?)'',
> task_ids])
>
> You can also do things like
>   task.declarations.average(:minutes)
The full calculations API --
   http://rails.outertrack.com/module/ActiveRecord%3A%3ACalculations% 
3A%3AClassMethods

jeremy

cies

2006-May-23 23:12 UTC

head link

[Rails] ad-hoc query

whoa, thanks for the tips ;)

actually i was using the following statement in a loop to get all the
ids in a tree that come after a particular parent.

ActiveRecord::Base.connection.execute("SELECT id FROM tasks WHERE
parent_id IN (#{str_result})")


But heay, thanks!
_c.

On 5/24/06, Jeremy Kemper <jeremy@bitsweat.net>
wrote:> On May 23, 2006, at 3:55 PM, Jeremy Kemper wrote:
> > On May 23, 2006, at 3:43 PM, cies wrote:
> >> ActiveRecord::Base.connection.execute("SELECT SUM(minutes)
FROM
> >> declarations WHERE task_id IN (#
> >> {task_ids_str})").fetch_row.first.to_i
> >
> > Hey, how about:
> >   Declaration.sum(:minutes, :conditions => [''task_id in
(?)'',
> > task_ids])
> >
> > You can also do things like
> >   task.declarations.average(:minutes)
>
> The full calculations API --
>    http://rails.outertrack.com/module/ActiveRecord%3A%3ACalculations%
> 3A%3AClassMethods
>
> jeremy
> _______________________________________________
> Rails mailing list
> Rails@lists.rubyonrails.org
> http://lists.rubyonrails.org/mailman/listinfo/rails
>

-- 
"Computer games don''t affect kids; I mean if Pac-Man affected us
as
kids, we''d all be running around in darkened rooms, munching magic
pills and listening to repetitive electronic music." -- Kristian
Wilson (Nintendo, Inc), 1989

Jeremy Kemper

2006-May-23 23:51 UTC

head link

[Rails] ad-hoc query

On May 23, 2006, at 4:12 PM, cies wrote:> whoa, thanks for the tips ;)
One more - acts_as_tree (if you haven''t already found it :)
   http://rails.outertrack.com/module/ActiveRecord%3A%3AActs%3A%3ATree 
%3A%3AClassMethods
   http://rails.outertrack.com/module/ActiveRecord%3A%3AActs%3A%3ATree 
%3A%3AInstanceMethods

> actually i was using the following statement in a loop to get all the
> ids in a tree that come after a particular parent.
>
> ActiveRecord::Base.connection.execute("SELECT id FROM tasks WHERE
> parent_id IN (#{str_result})")
Aha - what''s the source of str_result?

Maybe there''s a clean way to get the result you need without ad-hoc  
queries.

jeremy

cies

2006-May-24 00:07 UTC

head link

[Rails] ad-hoc query

> Aha - what''s the source of str_result?
it part of a loop:

  # this method returns all task in the branch that this task is the
base of including itself
  # it makes use of direct querying of the database
  # returned is an array with the task id''s as strings
  def all_tasks_in_branch
    tasks_in_branch = [id.to_s]
    str_result = "#{id},"

    begin
      str_result = str_result[0..-2]  # cut off the extra comma
      raw_result = ActiveRecord::Base.connection.execute("SELECT id
FROM tasks WHERE parent_id IN (#{str_result})")
      str_result = ''''
      raw_result.each do |row|
        str_result += "#{row},"
        tasks_in_branch += row
      end
    end until str_result.empty?

    tasks_in_branch
  end



indeed an acts_as_tree thing ;)

cheers!
Cies Breijs




-- 
"Computer games don''t affect kids; I mean if Pac-Man affected us
as
kids, we''d all be running around in darkened rooms, munching magic
pills and listening to repetitive electronic music." -- Kristian
Wilson (Nintendo, Inc), 1989

Reasonably Related Threads

Search for more apparently analagous threads

Rails - May 2006 - ad-hoc query

[Rails] ad-hoc query

[Rails] ad-hoc query

Re: ad-hoc query

[Rails] ad-hoc query

[Rails] ad-hoc query

[Rails] ad-hoc query

[Rails] ad-hoc query

[Rails] ad-hoc query

[Rails] ad-hoc query

Reasonably Related Threads