«
»

Querying Multiple Tables With ActiveRecord

The other day at work I was doing some data mining for our business intelligence folks. They needed to get some stats from the last year of billing transactions, and the task came to me. I had to hack up a perl script to query the archived data to find a certain type of transaction. Easy enough, the only minor issue was that since the data set was both huge and old, the table were broken up by month.

My perl script looked something like this (cleaned up for brevity):

@months = qw( 200901 200902 200903 ); # up to 201004
$sql = "select * from BillingMONTH where resultCode=40";
foreach my $month( @months ) {
$sql_full = $sql;
$sql_full =~ s/MONTH/$month/; # change the table name for each record
$sth = $dbh->prepare($sql_full) or die;
$sth->execute();
[ ... ] # retrieve the data, save/process it
}

I started wondering if these sorts of quick and dirty, but non-trivial scripts were easily doable in Ruby using Active Record.

I set up a similar situation on my system, a mysql database with 3 tables and a couple of rows of garbage data in them.  I knew that I could get a database connection set up very fast by making a throwaway rails app, setting up the models in it, and then using script/console as my interface, but I’ve honestly never use activerecord in a scripting environment.

After some messing around I came up with this:

#!/usr/bin/ruby
require 'rubygems'
require 'active_record'
 
ActiveRecord::Base.establish_connection(
   :adapter => "mysql",
   :username => "root",
   :password => "password",
   :database => "test"
)
 
class Test < ActiveRecord::Base 
end 
 
%w( Archive201001 Archive201002 Archive201003 ).each do |tablename|
   res = Test.find( :all, :from => tablename, :conditions => "name like 'test%'" );
   puts "Total results from #{tablename}: #{res.size.to_s}"
   # [...] other awesome processing on the res object
end

Running it gives me:

alan@phoenix:~/code/ruby$ ./testdb.rb
Total results from Archive201001: 3
Total results from Archive201002: 6
Total results from Archive201003: 3

Whohoo!  The one thing I couldn’t figure out is how to get rid of having to create a sub-class of the ActiveRecord::Base class.  I technically can do it with:

ActiveRecord::Base.connection.execute("select * from #{tablename}")

But I don’t know how to get this into the “object oriented” version to use the :conditions and :o rder and such.  However, the main point of can I replace perl and DBI for quick and dirty database scripting has most definitively been answered.

Next step: seeing how to do the same thing in Data Mapper (to keep @dkubb happy) :)

Related posts (maybe):

  1. Deeper Into ActiveRecord
  2. Using Enums For Constant Data in Rails and ActiveRecord
  3. Quick Way To Experiment With ActiveRecord

This entry was posted on Saturday, April 10th, 2010 at 2:42 pm and is filed under Code, Database, General. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.

One Response to “Querying Multiple Tables With ActiveRecord”

  1. Stephan Wehner Says:
    November 27th, 2010 at 11:52 pm

    Try naming your class after the table names.

    %w( Archive201001 Archive201002 Archive201003 ).each do |tablename|
    eval <<-END_EVAL
    class #{tablename} < ActiveRecord::Base
    end
    END_EVAL
    end

    Now

    Archive201001.count
    Archive201002.count
    Archive201003.count

    should all work. Looks a bit silly.

    Other way,

    %w( Archive201001 Archive201002 Archive201003 ).each do |tablename|
    Test.set_tablename(tablename)
    res = Test.find( :all, :conditions => "name like 'test%'" );
    puts "Total results from #{tablename}: #{res.size.to_s}"
    end

    (Haven't run the code / might have some syntax error still.

    Stephan

Leave a Reply