Forum: Ruby on Rails ad-hoc query

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.
Jeff C. (Guest)
on 2006-05-23 18:39
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
Chang Sau S. (Guest)
on 2006-05-23 20:06
(Received via mailing list)
It's 'find_by_sql', you can do a search on it at api.rubyonrails.com


Jeff C. 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 S.

http://blog.saush.com - brain dump
http://www.projectible.com - online project publishing
http://jaccal.sourceforge.net - smart card toolkit
Jack C. (Guest)
on 2006-05-23 22:34
(Received via mailing list)
_______________________________________________
Rails mailing list
removed_email_address@domain.invalid
http://lists.rubyonrails.org/mailman/listinfo/rails
cies (Guest)
on 2006-05-24 02:45
(Received via mailing list)
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 C. <removed_email_address@domain.invalid> wrote:
> query..."), but I can't seem to find the documentation on it. I know
>  This will return an array of hashes with the result set.
>
>


--
"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 K. (Guest)
on 2006-05-24 02:58
(Received via mailing list)
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 K. (Guest)
on 2006-05-24 03:04
(Received via mailing list)
On May 23, 2006, at 3:55 PM, Jeremy K. wrote:
>   task.declarations.average(:minutes)
The full calculations API --
   http://rails.outertrack.com/module/ActiveRecord%3A...
3A%3AClassMethods

jeremy
cies (Guest)
on 2006-05-24 03:16
(Received via mailing list)
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 K. <removed_email_address@domain.invalid> wrote:
> > You can also do things like
> 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 K. (Guest)
on 2006-05-24 03:54
(Received via mailing list)
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...
%3A%3AClassMethods
   http://rails.outertrack.com/module/ActiveRecord%3A...
%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 (Guest)
on 2006-05-24 04:06
(Received via mailing list)
> 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
This topic is locked and can not be replied to.