|
|
ActiveRecord and multiple sql statements
back
Ruby on Rails developers should be so familiar with the standard way of mapping database tables into business objects using ActiveRecord. This approach relies on inflection of the database table to dynamically discover and map columns into attributes on the business objects. The database table has to have a column called id, and the default table name is assumed to be the underscored version of the class name, but in plural e.g. Article maps to articles, and RailStation maps to rail_stations etc. This simplistic but powerful wrapping technique performs surprisingly well and should take care of the majority of real world use cases and eliminates the need for middle tier mapping tools such as Hibernate or Castor, as is the case in Java and C#.
There are ocassions where this technique is not adequate, although this is pretty rare. There are provisions in ActiveRecord to allow for the mapping of more complicated schemas into business object, although this is actively discouraged by the designer of Rails. You are essentially on your own if you choose to follow down this path, although the flexibility and complex design issues might sometimes justify this.
For the rest of the article, please refer to the schema diagram below for table names and object names.
The first apporach is to use the find_by_sql class method call on a derived class of ActiveRecord::Base. You need to ensure the returned data is a select * of the table in question. For example, let's say we want to locate all the at-the-money NASDAQ options in our database, we can do:
today = Time.now - (24*60*60)
sql = <<-EOL
select o.* from options o
join prices p on o.id = p.option_id
join exchanges e on e.id = o.exchange_id
join deltas d on o.id=d.option_id
where
e.name = 'NASDAQ'
and p.price > 0.0 and p.update_time>=#{today}
and (d.value >= 0.45 and d.value <= .55)
EOL
@at_the_money_options = Option.find_by_sql(sql)
Note that this method is a class method, not an instance method, so you have to prepend the method with the class name, but there is no need to create an instance object in order to invoke it.
You must also be careful in using this approach that if you are intending to return just one single row from the query, you will need to extract it from the list of returned rows
@option = @at_the_money_options.first
Granted, this can be done by calling Option.find(:all), then filtering through the resulting rows to find the ones which match our criteria, but doing this on the database server has two advantages: - You can add indices to various columns to performance tune the query
- the memory footprint of your web server process will be a lot smaller because you do not have to store all the returning rows of the options table (and possibly other tables too)
The second approach is even more radical. You can go one level deeper than ActiveRecord::Base.find_by_sql() to perform even queries which consist of multiple sql statements. This approach is a bit trickier to map into the collection of objects because you have to have the knowledge of which sql statement is responsible for returning the rows which can be mapped into the objects. To get to the resulting rows before they are mapped into the object, you need to use the methods ActiveRecord::Base.select_all() and ActiveRecord::Base.connection().Let's say, for example, that you want to find all the at the money options which also have underlying stocks that you are currently holding at ETrade:
today = Time.now - (24*60*60)
sql = <<-EOL
create temporary table etrade_positions select s.id from securities
join positions p on s.id = p.security_id
join brokers b on b.id = p.broker_id
where
p.quantity > 0 and
b.name = 'ETrade';
select o.* from options o
join prices p on o.id = p.option_id
join exchanges e on e.id = o.exchange_id
join deltas d on o.id=d.option_id
join etrade_positions ep on ep.id=o.security_id
where
e.name = 'NASDAQ'
and p.price > 0.0 and p.update_time>=#{today}
and (d.value >= 0.45 and d.value <= .55);
EOL
# split the sql into individual statements
statements = sql.split(/;/)
# obtain the underlying connection to our ActiveRecord object from a dummy
db_conn = Option.new.connection
# start to sql batch
db_conn.begin_db_transaction
@at_the_money_options = Array.new
statements.each do |statement|
rows = db_conn.select_all(statement)
# do not create the objects until we get to the last statement
next if statement != statements.last
rows.each do |row|
@at_the_money_options.push Option.new(row)
end
end
# commit our db batch
db_conn.commit_db_transaction
Please note that the above sql query was used to demonstrate the point, as you can probably do it just as well with a nested query or a stored procedure (if you happen to have MySQL 5, that is. I am still on the cretaceous version which does not even do nested queries).
back
by by David at 20 Apr 2006 17:25:22
|
|
