Ad-hoc query

Rails
1

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

2

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

3

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

4

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. [email protected] 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

5

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

6

whoa, thanks for the tips :wink:

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. [email protected] 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

7

On May 23, 2006, at 4:12 PM, cies wrote:

whoa, thanks for the tips :wink:

One more - acts_as_tree (if you haven’t already found it :slight_smile:
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

8

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%3ACalculations%
3A%3AClassMethods

jeremy

9

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 :wink:

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